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>JSmith@company.com</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-30T07:46:56.720

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 '16 at 07:46

answered Mar 29 '16 at 21:18

marc_s

732,580
175
1,330
1,459

This returns data, but only one row, where there should be hundreds – Sam Cohen-Devries Mar 29 '16 at 21:27
1

@Samcd: you didn't show *hundreds* of items of data in your example XML ..... updated my response - in that case, *assuming* it's the `` element that gets repeated, you need to call `.nodes('data-set/Row')` to get those *hundreds* of rows of data ..... – marc_s Mar 30 '16 at 04:47
Why do you think that the first argument in `.value()` (XPath expression) must be in paranthesis? – Shnugo Mar 30 '16 at 06:49
@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 – marc_s Mar 30 '16 at 07:48
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! – Shnugo Mar 30 '16 at 07:50
Thanks @marc_s. Processing XML in SQL always requires a little bit of guesswork for me, and I went through many different syntax combinations. I don't know how I missed this one. – Sam Cohen-Devries Mar 31 '16 at 20:13

score 2 · Answer 2 · answered Mar 29 '16 at 21:24

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>JSmith@company.com</Email>
        <AcctNo>123456</AcctNo>
    </Row>
    <Row>
        <Dept>DepartmentName</Dept>
        <FirstName>Alan</FirstName>
        <LastName>Wake</LastName>
        <Alias>AWake</Alias>
        <PhoneNo>7777777777</PhoneNo>
        <Email>AWake@company.com</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
------------------------- -------------------------
JSmith@company.com        John
AWake@company.com         Alan

(2 row(s) affected)

Querying values from XML nodes (possible NAMESPACE issue)

2 Answers2