SÉLECTIONNEZ le nœud valeurs de texte à partir du document xml en TSQL OPENXML

J'ai un document xml que je veux utiliser pour mettre à jour les valeurs dans une procédure stockée. Je peux traiter le XML à l'aide d'OPENXML, mais je suis confus au sujet de l'extraction de l'valeurs que je veux. Chaque ligne dans le fichier xml est un enregistrement de produit et je veux créer une variable pour chaque propriété. Cell0 est l'ID, Cellule2 description etc

DECLARE @idoc int  
DECLARE @doc varchar(1000)  
SET @doc ='
<products>    
 <rows>
  <row>
   <cell>1</cell>
   <cell>BALSAMO DERMOSCENT</cell>
   <cell>1.00</cell>
   <cell>0.00</cell>
   <cell>18.00</cell>
   <cell>18.00</cell>
   <cell>8.00</cell>
   <cell>427</cell>
   <cell>No</cell>
  </row>
  <row>
   <cell>2</cell>
   <cell>BAYTRIL 150 MG 1 CPDO</cell>
   <cell>1.00</cell>
   <cell>0.00</cell>
   <cell>3.50</cell>
   <cell>3.50</cell>
   <cell>8.00</cell>
   <cell>57</cell>
   <cell>No</cell>
  </row>
 </rows>
</products>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/products/rows/row/cell',1)
with (Col1 varchar(29) 'text()')

L'exécution de la requête ci-dessus retourne 1 enregistrement pour chaque CELLULE dans le xml. Je veux être en mesure de retourner 1 enregistrement par ligne avec des colonnes pour chaque cellule, quelque chose comme:-

Prod       Description              Qty
---------- --------------------     --------
1          BALSAMO DERMOSCENT       1.00  
2          BAYTRIL 150 MG 1 CPDO    1.00

Je suis en utilisant MSSQL 2008

OriginalL'auteur caroig | 2010-12-15

  1. 5

    Je suis venu avec le suivant qui fait le travail pour moi

    DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<products>
  <rows>
    <row>
      <cell>1</cell>
      <cell>BALSAMO DERMOSCENT</cell>
      <cell>1.00</cell>
      <cell>0.00</cell>
      <cell>18.00</cell>
      <cell>18.00</cell>
      <cell>8.00</cell>
      <cell>427</cell>
      <cell>No</cell>
    </row>
    <row>
      <cell>2</cell>
      <cell>BAYTRIL 150 MG 1 CPDO</cell>
      <cell>1.00</cell>
      <cell>0.00</cell>
      <cell>3.50</cell>
      <cell>3.50</cell>
      <cell>8.00</cell>
      <cell>57</cell>
      <cell>No</cell>
    </row>
  </rows>
</products>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/products/rows/row',1)
with (pLineNo int 'cell[1]/text()',
      pDesc varchar(50) 'cell[2]/text()',
      pQty float 'cell[3]/text()',
      pCost float 'cell[4]/text()',
      pPvp float 'cell[5]/text()',
      pTotal float 'cell[6]/text()',
      pIva float 'cell[7]/text()',
      pId int 'cell[8]/text()',
      pnoFact varchar(5) 'cell[9]/text()')
    Il était très curieux au sujet de moi-même. +1. Pourquoi ne pas aller de l'avant et accepter votre propre réponse 😉

    OriginalL'auteur caroig

  2. 0

    Pourquoi utiliser openxml sur sql server 2008?

    C'est une meilleure option (j'ai utilisé de type varchar(max), comme le type de données, mais de saisir tout ce qui est applicable). Remarque vous devez déclarer la variable comme xml, pas de type varchar.

    SELECT
 Row.Item.value('data(cell[1])', 'varchar(max)') As Prod,
 Row.Item.value('data(cell[2])', 'varchar(max)') As Description,
 Row.Item.value('data(cell[3])', 'varchar(max)') As Qty
FROM
 @doc.nodes('//row') AS Row(Item)

    Remarque: Si vous êtes en train de faire c'est une procédure stockée, vous devrez peut-être inclure les éléments suivants avant de l'instruction select:

    SET ARITHABORT ON -- required for .nodes

    Si vous devez utiliser openxml, au moins le nettoyer lorsque vous avez terminé:

    exec sp_xml_removedocument @idoc
    Quelle est la raison de l'exigence de SET ARITHABORT? Avez-vous des documents de référence qui indique quelles sont les conséquences de ne pas appliquer la présente?
    la requête ne serait pas exécuter autrement. Cela aurait été sql server 2005 à l'époque.

    OriginalL'auteur ScottE