Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

2 Answers

up vote 2 down vote

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

@InputXml.nodes('/Root/Element2[@Name = ''otherElementName'']//SubElement') t(c);
share|improve this answer
Would also work if the N were in the right spot, e.g. @InputXml.nodes(N'/Root/...' – Aaron Bertrand Mar 12 '12 at 22:35
Thank you @Aaron – G21 Mar 13 '12 at 15:23
up vote 0 down vote

Try this:

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

Result:

subelementFielName
share|improve this answer
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
Thank you @Aaron – G21 Mar 13 '12 at 15:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.