Querying values from XML nodes (possible NAMESPACE issue)

Question

I have worked with querying XML and I have what seems like it should be a pretty straightforward query.

My data is stored in XML format, such as this:

declare @data xml = 
'<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Row>
        <Dept>DepartmentName</Dept>
        <FirstName>John</FirstName>
        <LastName>Smith</LastName>
        <Alias>JSmith</Alias>
        <PhoneNo>5555555555</PhoneNo>
        <Email>[email protected]</Email>
        <AcctNo>123456</AcctNo>
    </Row>
    <Row>
    ...
    </Row>
</data-set>'

I am querying it using:

;WITH XMLNAMESPACES (DEFAULT 'http://www.w3.org/2001/XMLSchema-instance')
select
C.value('/Row[1]/Email[1]','varchar(max)')
C.value('/Row[1]/FirstName[1]','varchar(max)')
from @data.nodes('data-set') as T(C)

I have tried it with and without the '[1]' after the fields, without the NAMESPACE and I have tried querying using //Email[1] or @Email but am having no luck. Any suggestions?

marc_s · Accepted Answer · 2016-03-30 07:46:56Z

2

You're close - no namespace needed - but your syntax is slightly off - try this:

select
    C.value('Email[1]', 'varchar(255)'),
    C.value('FirstName[1]', 'varchar(25)')
FROM
    @data.nodes('data-set/Row') as T(C)

The XPath expression (first argument in the .value() call) needs to be in parenthesis - and it should not start with a / (since it's "relative" to the XML fragment returned by the .nodes() call).

edited Mar 30, 2016 at 7:46

answered Mar 29, 2016 at 21:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

6 Comments

Sam CD Over a year ago

This returns data, but only one row, where there should be hundreds

marc_s Over a year ago

@Samcd: you didn't show hundreds of items of data in your example XML ..... updated my response - in that case, assuming it's the <Row> element that gets repeated, you need to call .nodes('data-set/Row') to get those hundreds of rows of data .....

Gottfried Lesigang Over a year ago

Why do you think that the first argument in .value() (XPath expression) must be in paranthesis?

marc_s Over a year ago

@Shnugo: hmm.... surprisingly, you're right - they don't have to be in parenthesis..... hmmm.... I was under the impression that was necessary - but it seems I was wrong on that count

Gottfried Lesigang Over a year ago

Yeah! This day gets a big mark in my calendar! I could tell one of the first pagers something new! Schöne Frühlingsgrüße in die Schweiz!

|

gofr1 · Accepted Answer · 2016-03-29 21:24:39Z

declare @data xml = 
'<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Row>
        <Dept>DepartmentName</Dept>
        <FirstName>John</FirstName>
        <LastName>Smith</LastName>
        <Alias>JSmith</Alias>
        <PhoneNo>5555555555</PhoneNo>
        <Email>[email protected]</Email>
        <AcctNo>123456</AcctNo>
    </Row>
    <Row>
        <Dept>DepartmentName</Dept>
        <FirstName>Alan</FirstName>
        <LastName>Wake</LastName>
        <Alias>AWake</Alias>
        <PhoneNo>7777777777</PhoneNo>
        <Email>[email protected]</Email>
        <AcctNo>123457</AcctNo>
    </Row>
</data-set>'


select  T.C.value('(Email)[1]','varchar(max)') as Email,
        T.C.value('(FirstName)[1]','varchar(max)') as FirstName
from @data.nodes('/data-set/Row') as T(C)

Result:

Email                     FirstName
------------------------- -------------------------
[email protected]        John
[email protected]         Alan

(2 row(s) affected)

Collectives™ on Stack Overflow

Querying values from XML nodes (possible NAMESPACE issue)

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related