Why does this xml query in SQL Server not work

Question

Lately I was blown by xml for SQL Server, but this one here gets me nuts

DECLARE @xml XML;
SET @xml = '
    <ROOT>
        <Object>
            <FooProperty1>123</FooProperty1>
            <FooProperty2>456</FooProperty2>
        </Object>
        <Object>
            <FooProperty1>123</FooProperty1>
            <FooProperty2>456</FooProperty2>
        </Object>
    </ROOT>
';

SELECT  [doc].[FooProperty1].value('.', 'INT') AS [fooProperty1],
        [doc].[FooProperty2].value('.', 'INT') AS [fooProperty2]
FROM    @xml.nodes('/ROOT/Object')
AS      [doc]
(
    [FooProperty1],
    [FooProperty2]
)

Gives me

Msg 8159, Level 16, State 1, Line 22 'doc' has fewer columns than were specified in the column list.

Does msg does not change if I change change @xml as

SET @xml = '
    <ROOT>
        <Object FooProperty1="123" FooProperty2="456"/>
        <Object FooProperty1="123" FooProperty2="456"/>
    </ROOT>
';

marc_s · Accepted Answer · 2011-01-04 08:15:05Z

3

Your XQuery is not correct, I believe - try this instead:

SELECT  
    Root.Obj.value('(FooProperty1)[1]', 'INT') AS [fooProperty1],
    Root.Obj.value('(FooProperty2)[1]', 'INT') AS [fooProperty2]
FROM
    @xml.nodes('/ROOT/Object') AS Root(Obj)

The @xml.nodes() defines XML fragments - one for each entry in the XPath - and you need to assign it a two-identifier alias for a pseudo-table with a single column (format: Root(Obj)). That single column of that pseudo-table is the XML fragment your XPath query selected from the XML document.

Based on that alias, you can then reach into those XML fragments and pull out the various properties.

Update: for your second XML, with the XML attributes, use this instead:

SELECT  
    Root.Obj.value('(@FooProperty1)[1]', 'INT') AS [fooProperty1],
    Root.Obj.value('(@FooProperty2)[1]', 'INT') AS [fooProperty2]
FROM
    @xml.nodes('/ROOT/Object') AS Root(Obj)

By specifying @FooProperty1 you're grabbing the XML attribute instead of the XML sub-element from that <ROOT>/<Object> node you're currently looking at.

edited Jan 4, 2011 at 8:15

answered Jan 4, 2011 at 8:07

marc_s

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

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

2 Comments

user57508 Over a year ago

This approach does only work, if I am using the first pattern with extra elements for each column ... Could you maybe give me an example for the second pattern?

marc_s Over a year ago

@Andreas Niedermair: updated my response to also handle your second scenario (with XML attributes)

Collectives™ on Stack Overflow

Why does this xml query in SQL Server not work

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related