Données xml de Microsoft SQL Server

Question

Ce site a une technique pour faire circuler des données xml dans 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) 

Mais quelle est la syntaxe si j'ajoute un autre champ ? Ce qui suit ne fonctionne PAS :

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 : J'ai peut-être mal construit mon xml.

Answer 1

Vous devez utiliser quelque chose comme :

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

Cette déclaration FROM définit quelque chose comme une "table virtuelle" appelée ParamValues.ID - vous devez sélectionner le <Products> dans cette table virtuelle, puis d'accéder aux propriétés qu'elle contient.

De plus, votre structure XML est très mal choisie :

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

Vous ne pourrez pas sélectionner les paires individuelles d'id/descr - vous devriez utiliser quelque chose de plus semblable :

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

Vous pouvez ensuite récupérer tous les éléments en utilisant cette requête SQL XML :

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

Answer 2

Vous devez envelopper chaque ensemble de id y descr en un seul nœud parent. Disons Rangée . Maintenant, vous pouvez accéder à chaque paire comme ceci.

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)