T-SQL Convert XML field to multi-column dataset

Question

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?

Answer 1

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 output in my case (using a helper table).

See this SQL Fiddle as a demo

Answer 2

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.