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)
Leave a Reply