Geek Answers Handbook

T-SQL Converting an XML Field to a Multi-Column Dataset

I want to “split” an XML field into a multi-column dataset. XML is as follows:

<simple> <propertyid> <value>p1</value> <value>p2</value> <value>p3</value> <value>p4</value> </propertyid> <complexid> <value>c1</value> <value>c2</value> <value>c3</value> </complexid> </simple>

I tried to do something like this (every time I work with XML in TSQL, I feel butthurt, so my code is wrong):

 ;WITH source AS ( SELECT CAST(@xmlstring AS XML) AS x ) SELECT items.item.query('.') FROM source CROSS APPLY x.nodes('/simple/*/value') AS items(item)

Expected dataset:

 ColumnName Value ------------------------ propertyid p1 propertyid p2 propertyid p3 propertyid p4 complexid c1 complexid c2 complexid c3

How can I get the desired result? Maybe you can recommend a good resource with explanations and samples of T-SQL + XML + XQuery?

+4
source share
3 answers

And here is my own solution with 1 CROSS APPLY:

 ;WITH source AS ( SELECT CAST(@xmlstring AS XML) AS x ) SELECT items.item.value('local-name(..)', 'varchar(300)') AS ColumnName, items.item.value('text()[1]', 'varchar') AS Value FROM source CROSS APPLY x.nodes('/simple/*/value') AS items(item)

Basically, I was unable to access the parent node, I tried using user '../ local-name ()', but local-name () requires XPath for the name diplay. After that, show parents the name node becomes easy.

+3
source

Try something like this:

 ;WITH source AS ( SELECT CAST(@xmlstring AS XML) AS x ) SELECT ColumnName = XTbl.Parent.value('local-name(.)', 'varchar(50)'), [Value] = XTbl2.ChildValues.value('(.)[1]', 'varchar(20)') FROM source CROSS APPLY x.nodes('/simple/*') AS XTbl(Parent) CROSS APPLY XTbl.Parent.nodes('value') AS XTbl2(ChildValues)

Does this work for you? I get the desired result in my case (using a helper table).

See this SQL script as a demo

+5
source

As you requested the XQuery proposal and solution, here it is:

 declare function local:createTestDoc() as node()* { <simple> <propertyid> <value>p1</value> <value>p2</value> <value>p3</value> <value>p4</value> </propertyid> <complexid> <value>c1</value> <value>c2</value> <value>c3</value> </complexid> </simple> }; declare function local:doProcessing($arg as node()*) as xs:string* { let $simpleElement := $arg return ( concat("ColumnName", " ", "Value") , for $propertyId in $simpleElement/propertyid/* return ( concat("propertyid", " ", data($propertyId)) ) , for $complexId in $simpleElement/complexid/* return ( concat("complexid", " ", data($complexId)) ) ) }; local:doProcessing(local:createTestDoc())

Instead of using local: createTestDoc () you will have to include your document using something like:

 declare variable $myDoc := doc("urlToDoc");

and pass it to local: doProcessing () fn.

 local:doProcessing($myDoc)
+1
source

More articles:


All Articles