2

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.

Community
  • 1
  • 1
G21
  • 1,297
  • 2
  • 18
  • 39

2 Answers2

2

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

@InputXml.nodes('/Root/Element2[@Name = ''otherElementName'']//SubElement') t(c);
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Bartosz Bąkowski
  • 39
  • 5
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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 1
    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. – Aaron Bertrand Mar 12 '12 at 22:36