SQL Server XML Data Retrieval Using XQuery

SQL Server allows that storage of XML in table columns.

The XML can be stored in either a plain text data type or as valid XML.

The most efficient way of accessing the XML is using a temporary table to store XML data.

The main reason is that the column may not be an XML data type and it also allows more control over the output.

XML Text Values

The XML text is a value specified between the start and end tags.

USE DATABASE
DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable
SELECT
[Book].[XmlBook]
FROM [Book];
SELECT
XmlDom.Element.value('Book/(@Title)[1]',
'VARCHAR(100)') AS [Title],
XmlDom.Element.value('Book/(@Author)[1]', 
'VARCHAR(100)') AS [Author]
FROM @XmlTable
CROSS APPLY XmlResult.nodes('//DATA/Books') AS XmlDom(Element)

XML Attribute Values

An XML attribute is an specified identifier within the XML element.

USE DATABASE
DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable
SELECT
[Book].[XmlBook]
FROM [Book];
SELECT
XmlDom.Element.value('data(Book[@Name="Title"]/@Value)[1]',
'VARCHAR(100)') AS [Title],
XmlDom.Element.value('data(Book[@Name="Author"]/@Value)[1]',
'VARCHAR(100)') AS [Author]
FROM @XmlTable
CROSS APPLY XmlResult.nodes('//DATA/Books') AS XmlDom(Element)

A temporary table is created then XML data is added to the XmlTable.XmlResult column.

The values are then retrieved using a SELECT statement and traversing the Nodes in the XML DOM stored in the column.

Flatten XML Data

A common issue with with XML data displayed as a table is that each element in a single entity is a row.

The following places each attribute into a separate column for each entity row.

USE DATABASE
DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable
SELECT [Book].[XmlBook]
FROM [Book]; 
SELECT XmlDom.Element.value('data(Book[@Name="Title"]/@Value)[1]',
'VARCHAR(MAX)') AS [Title],
XmlDom.Element.value('data(Book[@Name="Author"]/@Value)[1]',
'VARCHAR(MAX)') AS [Author],
XmlDom.Element.value('data(Book[@Name="ISBN"]/@Value)[1]', 
'VARCHAR(MAX)') AS [ISBN]
FROM @XmlTable
CROSS APPLY XmlResult.nodes('//DATA/Books') AS XmlDom(Element) 

Posted

in

,

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.