CODEWITHSUNDEEP
sql-serverxml
user57508
user57508

Reputation:

Why does this xml query in SQL Server not work

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>
';

Upvotes: 1

Views: 868

Answers (1)

marc_s
marc_s

Reputation: 754258

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.

Upvotes: 3

Related Questions