Microsoft SQL Server xml data

Question

This site has a technique to pass xml data around in Microsoft SQL Server:

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>' 

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)

But what is the syntax if I add another field? The following does NOT work:

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><descr>Three</descr><id>6</id><descr>six</descr><id>15</id><descr>Fifteen</descr></Products>' 

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
,ParamValues.descr.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)

Note: Maybe I've constructed my xml wrong.

marc_s · Accepted Answer · 2010-04-21 17:10:15Z

You need to use something like:

SELECT
    ParamValues.ID.value('(id)[1]','VARCHAR(20)'),
    ParamValues.ID.value('(descr)[1]','VARCHAR(20)')
FROM
    @productIds.nodes('/Products') as ParamValues(ID)

That FROM statement there defines something like a "virtual table" called ParamValues.ID - you need to select the <Products> node into that virtual table and then access the properties inside it.

Furthermore, your XML structure is very badly chosen:

<Products>
    <id>3</id>
    <descr>Three</descr>
    <id>6</id>
    <descr>six</descr>
    <id>15</id>
    <descr>Fifteen</descr>
</Products>

You won't be able to select the individual pairs of id/descr - you should use something more like:

<Products>
    <Product>
       <id>3</id>
       <descr>Three</descr>
    </Product>
    <Product>
       <id>6</id>
       <descr>six</descr>
    </Product>
    <Product>
       <id>15</id>
       <descr>Fifteen</descr>
    </Product>
</Products>

Then you could retrieve all items using this SQL XML query:

SELECT
    ParamValues.ID.value('(id)[1]','VARCHAR(20)') AS 'ID',
    ParamValues.ID.value('(descr)[1]','VARCHAR(20)') AS 'Description'
FROM
    @productIds.nodes('/Products/Product') as ParamValues(ID) 

ID    Descrition
 3      Three
 6      six
15      Fifteen

A G · Accepted Answer · 2010-04-21 17:33:10Z

1

You must wrap each set of id and descr into one parent node. Say Row. Now you can access each pair like this.

DECLARE @productIds xml
SET @productIds ='<Products><Row><id>3</id><descr>Three</descr></Row><Row><id>6</id><descr>six</descr></Row><Row><id>15</id><descr>Fifteen</descr></Row></Products>' 

SELECT
ParamValues.Row.query('id').value('.','VARCHAR(20)'),
ParamValues.Row.query('descr').value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/Row') as ParamValues(Row)

answered Apr 21, 2010 at 17:33

A G

22.9k12 gold badges94 silver badges113 bronze badges

Collectives™ on Stack Overflow

Microsoft SQL Server xml data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related