I have covered few examples on how to read XML data in sql server. If you have more scenerios, please post it in the comments ...
Here are some basic examples of using XQuery and XPath in SQL Server to work with XML data.
Setting Up XML Data in SQL Server
First, let’s create a simple table with an XML column and insert some sample XML data.
CREATE TABLE Books (
ID INT PRIMARY KEY,
BookDetails XML
);
INSERT INTO Books (ID, BookDetails)
VALUES
(1, '<book><title>Learning SQL</title><author>John Doe</author><year>2022</year></book>'),
(2, '<book><title>Advanced SQL</title><author>Jane Smith</author><year>2023</year></book>');
Example 1: Querying XML Data with XPath
You can use the value() method to extract specific values from the XML data.
Query to Get Book Titles
SELECT
BookDetails.value('(/book/title)[1]', 'nvarchar(100)') AS Title
FROM
Books
WHERE
ID = 1;
Example 2: Querying Multiple Values
You can use the nodes() method to shred the XML and retrieve multiple values.
Query to Get All Book Titles and Authors
SELECT
b.value('(title)[1]', 'nvarchar(100)') AS Title,
b.value('(author)[1]', 'nvarchar(100)') AS Author
FROM
Books
CROSS APPLY
BookDetails.nodes('/book') AS T(b);
Example 3: Filtering with XQuery
You can filter the XML data using conditions in your query.
Query to Get Books Published After 2022
SELECT
BookDetails.value('(/book/title)[1]', 'nvarchar(100)') AS Title
FROM
Books
WHERE
BookDetails.value('(/book/year)[1]', 'int') > 2022;
Example 4: Modifying XML Data
You can also update XML data using the modify() method.
Update a Book's Title
UPDATE Books
SET
BookDetails.modify('replace value of (/book/title)[1] with "Learning SQL Updated"')
WHERE
ID = 1;
Example 5: Adding New Elements
You can add new elements to your XML data using the modify() method.
Add a New Element for Price
UPDATE Books
SET
BookDetails.modify('insert <price>29.99</price> as last into /book')
WHERE
ID = 1;
Conclusion
These examples illustrate how to use XQuery and XPath in SQL Server to work with XML data. You can extract, filter, update, and manipulate XML data effectively, making it a powerful feature for applications that require XML handling.
