CODEWITHSUNDEEP
sql-serverxmlxml-parsing
Emka
Emka

Reputation: 350

Parse XML with Attributes

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)

Upvotes: 1

Views: 199

Answers (1)

Thom A
Thom A

Reputation: 95561

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);

Upvotes: 1

Related Questions