How to read data from an XML variable?

Question

I've an xml variable with the below given format, and I do need to dinamically read and load into some T-SQL structure the element names and its respective fields values:

declare @InputXml   xml
Set @InputXml = '<Root RootName="rooNameValueHere">
            <Element Name="elementName">
                <SubElement Field="subelementFielName"/>
            </Element>
            <Element Name="otherElementName">
                <SubElement Field="subelementFielName1"/>
                <SubElement Field="subelementFielName2"/>
                <SubElement Field="subelementFielName3"/>
            </Element>
        </Root>'

Tips: 1 - The Root can contain N Elements 2 - Each Element can contain N SubElements 3 - I need to create a routine able to retrieve both all the information and also able to filter out by providing one Element Name (e.i.: for a given element, return all its subElements).

Right now, I've written this T-SQL to get subElements given one element name but I'm getting a null value:

select 
    t.c.value('@Field', 'nvarchar(10)') AS SomeAlias
from
    @InputXml.nodes('N/Root/Element[@Name = ''elementName'']//SubElement') t(c);

Resolution:

This post help me to clear out my issue. Thanks to all who responded.

Aaron Bertrand · Accepted Answer · 2012-03-12 22:34:52Z

2

I removed 'N' char from beginning of query and it works :)

@InputXml.nodes('/Root/Element2[@Name = ''otherElementName'']//SubElement') t(c);

edited Mar 12, 2012 at 22:34

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

answered Mar 12, 2012 at 22:10

Bartosz Bąkowski

395 bronze badges

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

1 Comment

Aaron Bertrand Over a year ago

Would also work if the N were in the right spot, e.g. @InputXml.nodes(N'/Root/...'

Mikael Eriksson · Accepted Answer · 2012-03-13 06:44:07Z

1

Try this:

select 
    t.c.value('@Field', 'nvarchar(100)') AS SomeAlias
from
    @InputXml.nodes(N'/Root/Element[@Name = "elementName"]/SubElement') t(c);

Result:

subelementFielName

edited Mar 13, 2012 at 6:44

answered Mar 12, 2012 at 22:10

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

1 Comment

Aaron Bertrand Over a year ago

I think the N should still be at the beginning (just in the right spot) to cover the outside chance that true Unicode data is ever contained in, say, an element name.

Collectives™ on Stack Overflow

How to read data from an XML variable?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related