T-SQL Convert XML field to multi-column dataset

t-sql, xml, xquery

Solution

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)

Mainly I failed to access parent node, I tried to user '../local-name()', but local-name() requires XPath to diplay name. After that show parents node name becomes easy.

Problem

I want to 'split' XML field to the multiple columns dataset. XML looking like: ``` <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 that (each time I working 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 required result? Maybe you can recommend good resource with T-SQL + XML + XQuery explanations and samples?

Original source