SQLXML Gymnastics

Producing well-formed XML from SQL:

SELECT

ID,

Name as 'Names/FirstName',

Surname as 'Names/LastName'

FROM

Names

FOR XML PATH('person'), ROOT('people')

• http://stackoverflow.com/questions/3184346/neat-xml-from-t-sql
• http://blah.winsmarts.com/2007-2-Bak2Basics__Learn_T-SQL_-_FOR_XML.aspx

Converting XML into a table:

Input:

<Elements>

<Element>

<Index>1</Index>

<Type>3M</Type>

<Code>AL</Code>

<Time>1900-01-01T10:22:00</Time>

</Element>

<Element>

<Index>2</Index>

<Type>3M</Type>

<Code>AA</Code>

<Time>1900-01-01T19:00:00</Time>

</Element>

</Elements>

SQL:

SELECT

r.value('Index[1]', 'int') [Index],

r.value('Type[1]', 'nvarchar(50)') [Type],
r.value('Code[1]', 'nvarchar(50)') [Code],
r.value('Time[1]', 'datetime') [Time]

FROM

@content.nodes('/Elements/*') AS records(r)

Converting XML into a pivot table:

Input:

<Elements>

<Element>

<Index>1</Index>

<Type>3M</Type>

<Code>AL</Code>

<Time>1900-01-01T10:22:00</Time>

</Element>

<Element>

<Index>2</Index>

<Type>3M</Type>

<Code>AA</Code>

<Time>1900-01-01T19:00:00</Time>

</Element>

</Elements>

SQL:

SELECT

El.Elem.value('(Index)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(.)', 'varchar(100)') AS 'Field Name',

SubEl.SubElem.value('.', 'varchar(100)') AS 'Field Value'

FROM

@content.nodes('/Elements/Element') AS El(elem)

CROSS APPLY

El.Elem.nodes('*') AS SubEl(SubElem)

WHERE

SubEl.SubElem.value('local-name(.)', 'varchar(100)') <> 'Index'

Produces:

Index	Field Name	Field Value
1	Type	3M
1	Code	AL
1	Time	1900-01-01T10:22:00
2	Type	3M
2	Code	AA
2	Time	1900-01-01T19:00:00

Extracting attribute names (plus parent element name of each attribute):

SELECT

elem.value('local-name(..)', 'nvarchar(10)') AS 'Parent Name',

elem.value('local-name(.)', 'nvarchar(10)') AS 'Attribute Name',

elem.value('.', 'nvarchar(10)') AS 'Attribute Value'

FROM

@content.nodes('//@*') AS El(elem)

• http://stackoverflow.com/questions/3193956/how-to-get-every-attributes-name-value-and-parent-element-name-in-sqlxml

Extracting field names:

SELECT DISTINCT r.value('fn:local-name(.)', 'nvarchar(50)') FieldName

FROM @xml.nodes('/*/*') AS records(r)

Extracting nodes as XML:

SELECT

pref.query('.') as SomeXml,

FROM

@xml.nodes('/*/*') AS Content(pref)

Extracting nodes as XML with indices only if they have child nodes:

SELECT

row_number() over(order by cast(pref.query('.') as nvarchar(max))) as 'RowNum',

pref.query('.') as XmlExtract

FROM

@xml.nodes('/*/*') AS extract(pref)

WHERE

pref.value('./*[1]', 'nvarchar(10)') IS NOT NULL

• http://stackoverflow.com/questions/3195193/select-xml-nodes-as-xml-in-t-sql
• http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

Various extractions:

Given:

DECLARE @content XML

SET @content =

'<people>

<person id="1" bimble="1">

<firstname bobble="gomble">John</firstname>

<surname>Doe</surname>

</person>

<person id="2" bimble="11">

<firstname bobble="zoom">Mary</firstname>

<surname>Jane</surname>

</person>

<person id="4" bimble="10">

<firstname bobble="womble">Matt</firstname>

<surname>Spanner</surname>

</person>

</people>'

Return:

-- All attributes with parent element name

SELECT

elem.value('local-name(..)', 'nvarchar(10)') AS 'Parent Name',

elem.value('local-name(.)', 'nvarchar(10)') AS 'Attribute Name',

elem.value('.', 'nvarchar(10)') AS 'Attribute Value'

FROM

@content.nodes('//@*') AS El(elem)

-- Inner element values (with index attribute)

SELECT

El.Elem.value('(@*)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(.)', 'nvarchar(10)') AS 'Field Name',

SubEl.SubElem.value('.', 'nvarchar(10)') AS 'Field Value'

FROM

@content.nodes('/*/*') AS El(elem)

CROSS APPLY

El.Elem.nodes('*') AS SubEl(SubElem)

-- Second level element attributes (with index attribute)

SELECT

El.Elem.value('(@*)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(.)', 'nvarchar(10)') AS 'Field Name',

SubEl.SubElem.value('.', 'nvarchar(10)') AS 'Field Value'

FROM

@content.nodes('/*/*') AS El(elem)

CROSS APPLY

El.Elem.nodes('@*') AS SubEl(SubElem)

-- Third level element attributes (with index attribute)

SELECT

El.Elem.value('(@*)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(.)', 'nvarchar(10)') AS 'Field Name',

SubEl.SubElem.value('.', 'nvarchar(10)') AS 'Field Value'

FROM

@content.nodes('/*/*') AS El(elem)

CROSS APPLY

El.Elem.nodes('*/@*') AS SubEl(SubElem)

-- All element attributes and parent element name (with index attribute)

SELECT DISTINCT

El.Elem.value('(@*)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(..)', 'nvarchar(10)') AS 'Parent Name',

SubEl.SubElem.value('local-name(.)', 'nvarchar(10)') AS 'Field Name',

SubEl.SubElem.value('.', 'nvarchar(10)') AS 'Field Value'

FROM

@content.nodes('/*/*') AS El(elem)

CROSS APPLY

El.Elem.nodes('//@*') AS SubEl(SubElem)

ORDER BY [Index]

External references:

• http://beyondrelational.com/blogs/jacob/archive/2010/05/30/select-from-xml.aspx
• http://stackoverflow.com/questions/3180834/retrieving-xml-element-name-using-t-sql/3180868#3180868
• http://stackoverflow.com/questions/2266132/how-can-i-get-a-list-of-element-names-from-an-xml-value-in-sql-server
• http://www.stylusstudio.com/sqlxml_tutorial.html
• http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

One last thing...

I got the XML to paste properly by using this link to encode the XML into HTML:

• http://centricle.com/tools/html-entities/