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)
精彩评论