开发者

Microsoft SQL Server xml data

This site has a technique to pass xml data around 开发者_如何学Goin 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.


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


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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜