Parse XML with Attributes

Question

Given an XML with this structure:

<someStructure>
    <Tag1 Qualifier = "QQ" Name = "Name">
        <Tag2 Code = "CD" Name = "SomeName">
            <Tag3 Qualifier = "QW" Number = "22"/>
        </Tag2>
    </Tag1>
    <Tag1 Qualifier = "NN" Name = ""/>
    <Tag1 Qualifier = "QE" Name = "Name2">
        <Tag2 Code = "CD" Name = "SomeName2">
            <Tag3 Qualifier = "QW" Number = "22"/>
        </Tag2>
    </Tag1>
</someStructure>

Is it possible to parse the results with 1 query?

I can parse each row with XML.nodes and taking it's value() but I cannot fetch the results for child nodes.

I want to do something like this

SELECT b.value('@Qualifier', 'nvarchar(max)'),b.value('@Name', 'nvarchar(max)'),
b.value('/Tag2/@Code', 'nvarchar(max)'),b.value('/Tag2/@Name', 'nvarchar(max)'),
b.value('/Tag2/Tag3/@Qualifier', 'nvarchar(max)'),b.value('/Tag2/Tag3/@Number', 'nvarchar(max)')
FROM @xml.nodes('/someStructure/Tag1') as a(b)

But I am getting this error:

Msg 2389, Level 16, State 1, Procedure spParse_XML, Line 96 [Batch Start Line 0] XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

I have managed to get the expected values by parsing each row in different query, but I am sure that cannot be the best way to do it..

SELECT b.value('@Qualifier', 'nvarchar(max)'),b.value('@Name', 'nvarchar(max)')
FROM @xml.nodes('/someStructure/Tag1') as a(b)

SELECT b.value('@Code', 'nvarchar(max)'),b.value('@Name', 'nvarchar(max)')
FROM @xml.nodes('/someStructure/Tag1/Tag2') as a(b)
    
SELECT b.value('@Qualifier', 'nvarchar(max)'),b.value('@Name', 'nvarchar(max)')
FROM @xml.nodes('/someStructure/Tag1/Tag2/Tag3') as a(b)

Dale K · Accepted Answer · 2021-07-06 10:12:59Z

1

As the error tells you, you haven't defined the singleton, which defines what numbered node the value is from. Most likely you just want the 1st node. I also changed your data types, as I doubt your values could all be up to 1 billion characters long (though I use an nvarchar(2) for most, which may not be long enough in all places).

This gives something like the following:

DECLARE @XML xml = '<someStructure>
    <Tag1 Qualifier = "QQ" Name = "Name">
        <Tag2 Code = "CD" Name = "SomeName">
            <Tag3 Qualifier = "QW" Number = "22"/>
        </Tag2>
    </Tag1>
    <Tag1 Qualifier = "NN" Name = ""/>
    <Tag1 Qualifier = "QE" Name = "Name2">
        <Tag2 Code = "CD" Name = "SomeName2">
            <Tag3 Qualifier = "QW" Number = "22"/>
        </Tag2>
    </Tag1>
</someStructure>';

SELECT sS.T1.value('(@Qualifier)[1]','nvarchar(2)') AS Tag1Qualifier, --Does this "need" to be up to 1 billion characters long?
       sS.T1.value('(@Name)[1]','nvarchar(20)') AS Tag1Name, --Does this "need" to be up to 1 billion characters long?
       sS.T1.value('(Tag2/@Code)[1]','nvarchar(2)') AS Tag2Code, --Does this "need" to be up to 1 billion characters long?
       sS.T1.value('(Tag2/@Name)[1]','nvarchar(20)') AS Tag2Name, --Does this "need" to be up to 1 billion characters long?
       sS.T1.value('(Tag2/Tag3/@Qualifier)[1]','nvarchar(2)') AS Tag3Qualifier, --Does this "need" to be up to 1 billion characters long?
       sS.T1.value('(Tag2/Tag3/@Number)[1]','int') AS Tag3Number --Does this "need" to be up to 1 billion characters long?
FROM (VALUES(@XML))V(X)
     CROSS APPLY V.X.nodes('/someStructure/Tag1') sS(T1);

edited Jul 6, 2021 at 10:12

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Jul 6, 2021 at 9:15

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlieface Over a year ago

Attributes themselves don't need [1] because there can only be one of them per node, only the nodes themselves need it

Collectives™ on Stack Overflow

Parse XML with Attributes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related