XML Data manipulation

Comprehensive Guide to XML Data Manipulation in SQL Server and Oracle PL/SQL

by

This guide will delve into the essentials of XML data manipulation in SQL Server and Oracle PL/SQL, two of the leading relational database systems.

Understanding how to manipulate XML data within relational databases like Microsoft SQL Server and Oracle’s PL/SQL is crucial for developers dealing with complex data structures. This skill not only enhances data storage and retrieval efficiency but also enables the dynamic modification of data, catering to the evolving needs of modern applications. In this post, we’ll explore the core techniques and best practices for working with XML in databases, ensuring you have the knowledge to fully leverage this powerful feature.

XML data manipulation is a powerful feature in relational databases, allowing developers to store, query, and dynamically modify structured data. In this blog post, we’ll explore how to work with XML data in Microsoft SQL Server (MSSQL) and Oracle’s PL/SQL. We’ll cover the basics of creating XML columns, inserting data, and dynamically updating XML values.

Mastering XML Data Manipulation in MSSQL

Creating and Inserting XML Data

In Microsoft SQL Server, handling XML data begins with understanding how to create and insert XML data into your database. SQL Server provides robust support for XML data types, allowing you to store XML documents or fragments in a column of the type XML.

CREATE TABLE XMLDemo (
    ID INT PRIMARY KEY,
    XmlData XML
);

Inserting XML Data:

Once you have a table with an XML column, you can insert XML data into it. Ensure your XML content is well-formed to avoid errors during insertion.


INSERT INTO XMLDemo (ID, XMLData) VALUES (1001, '<row id="1001"><r1>123</r1><r2>456</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1002, '<row id="1002"><r1>789</r1><r2>012</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1003, '<row id="1003"><r1>456</r1><r2>789</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1004, '<row id="1004"><r1>012</r1><r2>345</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1005, '<row id="1005"><r1>987</r1><r2>654</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1006, '<row id="1006"><r1>321</r1><r2>876</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1007, '<row id="1007"><r1>555</r1><r2>111</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1008, '<row id="1008"><r1>888</r1><r2>333</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1009, '<row id="1009"><r1>444</r1><r2>222</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1010, '<row id="1010"><r1>666</r1><r2>999</r2></row>');

Remember, when inserting XML data, it’s crucial to ensure that the XML is correctly formatted. SQL Server does not automatically validate XML data against an XML schema unless explicitly defined.

Dynamic XML Data Updates

Updating XML data stored in SQL Server requires familiarity with the modify() method of the XML data type. This method allows you to insert, update, or delete parts of your XML document using XQuery language.

Updating an Element: To change the value of an existing element, you might use the replace value of command with the modify() method:


UPDATE XMLDemo
SET XmlData.modify('
    replace value of (/row/r1/text())[1]
    with "New Value"
')
WHERE ID = 1001;

Here code snippet demonstrates the use of the .modify() method to replace the value of an XML node.

XML Data Manipulation in Oracle PL/SQL

Working with XMLType for XML Data

Oracle Database provides the XMLType data type to store and manipulate XML data efficiently. XMLType enables you to leverage the full spectrum of XML functionalities within Oracle, including querying, transforming, and validating XML data.

Creating a Table with XMLType Column:

To store XML data in Oracle, you can define a table with an XMLType column as follows:

CREATE TABLE XMLDemo (
    ID NUMBER PRIMARY KEY,
    XmlData XMLType
);


Inserting XML Data:

Inserting data into an XMLType column is straightforward. Use the XMLType constructor to ensure the XML data is properly handled:

INSERT INTO XMLDemo (ID, XMLData) VALUES (1001, XMLTYPE(‘<row id=”1001″><r1>123</r1><r2>456</r2></row>’));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1002, XMLTYPE(‘<row id=”1002″><r1>789</r1><r2>012</r2></row>’));

Updating Multiple XML Nodes

Modifying XML data in Oracle can be achieved using the XMLType methods such as extract(), transform(), and the standard SQL UPDATE statement. For updating multiple nodes within an XML document stored in an XMLType column, Oracle provides powerful functions that can be combined with XQuery.

Example – Updating Multiple Nodes: Suppose you want to update the r1 and r2 nodes in your XML data. You can use the UPDATEXML function for this purpose:

UPDATE XMLDemo
SET XMLData= XMLType.updateXML(
    XMLData,
    '/row/r1/text()',
    'Updated Value'
)
WHERE ID = 1001;

Here, the XMLType.updateXML() method is used to modify the XML value.

Updating Multiple XML Nodes: in MSSQL

In MSSQL, updating multiple nodes involves adjusting the XPath expressions. With a single update command making use of select subquery this can be achieved.

-- Create a table with an XML column
CREATE TABLE XMLDemo (
    ID INT PRIMARY KEY,
    XmlData XML
);


-- Insert XML data with four nodes
INSERT INTO XMLDemo (ID, XMLData) VALUES (1001, '<row id="1001"><r1>123</r1><r2>456</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1002, '<row id="1002"><r1>789</r1><r2>012</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1003, '<row id="1003"><r1>456</r1><r2>789</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1004, '<row id="1004"><r1>012</r1><r2>345</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1005, '<row id="1005"><r1>987</r1><r2>654</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1006, '<row id="1006"><r1>321</r1><r2>876</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1007, '<row id="1007"><r1>555</r1><r2>111</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1008, '<row id="1008"><r1>888</r1><r2>333</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1009, '<row id="1009"><r1>444</r1><r2>222</r2></row>');
INSERT INTO XMLDemo (ID, XMLData) VALUES (1010, '<row id="1010"><r1>666</r1><r2>999</r2></row>');


UPDATE XMLDemo
SET XMLData = (
    SELECT 
        (XMLData.query('/row/r1').value('.', 'NVARCHAR(MAX)') + ' Added By') as r1,
        (XMLData.query('/row/r2').value('.', 'NVARCHAR(MAX)') + ' Added By') as r2
    FOR XML PATH('row'), TYPE
)
WHERE ID = 1001;

Retrieve the XML data in MSSQL

SELECT ID, CustXmlData
FROM Customer
WHERE ID = 1;

Updating Multiple Nodes With Concatenating Existing Value: PL/SQL

When working with XML data in databases, a common requirement is not only to update multiple nodes but to do so by concatenating new values with the existing ones. This operation is particularly useful in scenarios where you want to append data to a list, add information without overwriting, or maintain a history of changes within the same XML structure.

Updating multiple nodes with concatenating the exciting value can be done using below PL/SQL code

CREATE TABLE XMLDemo (
    ID NUMBER PRIMARY KEY,
    XmlData XMLType
);

INSERT INTO XMLDemo (ID, XMLData) VALUES (1001, XMLTYPE('<row id="1001"><r1>123</r1><r2>456</r2></row>'));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1002, XMLTYPE('<row id="1002"><r1>789</r1><r2>012</r2></row>'));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1003, XMLTYPE('<row id="1003"><r1>456</r1><r2>789</r2></row>'));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1004, XMLTYPE('<row id="1004"><r1>012</r1><r2>345</r2></row>'));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1005, XMLTYPE('<row id="1005"><r1>987</r1><r2>654</r2></row>'));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1006, XMLTYPE('<row id="1006"><r1>321</r1><r2>876</r2></row>'));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1007, XMLTYPE('<row id="1007"><r1>555</r1><r2>111</r2></row>'));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1008, XMLTYPE('<row id="1008"><r1>888</r1><r2>333</r2></row>'));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1009, XMLTYPE('<row id="1009"><r1>444</r1><r2>222</r2></row>'));
INSERT INTO XMLDemo (ID, XMLData) VALUES (1010, XMLTYPE('<row id="1010"><r1>666</r1><r2>999</r2></row>'));


BEGIN
    FOR rec IN (SELECT ID, XMLData FROM XMLDemo WHERE XMLData IS NOT NULL) LOOP
        rec.XMLData := XMLTYPE(
            '<row><r1>' || rec.XMLData.extract('/row/r1/text()').getStringVal() || ' Added By User</r1>' ||
            '<r2>' || rec.XMLData.extract('/row/r2/text()').getStringVal() || ' Added By User</r2></row>'
        );

        -- Updating the XMLData column
        UPDATE XMLDemo
        SET XMLData = rec.XMLData
        WHERE ID = rec.ID;
    END LOOP;

    COMMIT;
END;

Conclusion:

Working with XML data in MSSQL and PL/SQL opens up a world of possibilities for managing structured information. Whether you’re in the SQL Server or Oracle ecosystem, dynamic manipulation of XML data can be achieved with ease. By understanding the XML data type and using the appropriate methods, you can create, insert, and update XML data seamlessly in your database applications.

In conclusion, XML data manipulation is a valuable skill for developers working with relational databases, providing flexibility and efficiency in handling structured information. Whether you’re diving into XML in MSSQL or PL/SQL, these fundamentals will empower you to harness the full potential of XML data in your database applications.

Leave a Comment