Comment Insérer des données xml dans SQL Server le tableau?

Comment importer ci-dessous des données XML dans SQL Server le tableau avec trois colonnes?

<dataset> 
 <metadata>
  <item name="NAME_LAST" type="xs:string" length="62" /> 
  <item name="NAME_FIRST" type="xs:string" length="62" /> 
  <item name="NAME_MIDDLE" type="xs:string" length="32" />
 </metadata>
<data>
<row>
 <value>SMITH</value> 
 <value>MARY</value> 
 <value>N</value> 
</row>
<row>
 <value>SMITH2</value> 
 <value>MARY2</value> 
 <value>N2</value> 
</row>
</data>
</dataset>
Ce n'est pas une réponse, essayez de regarder d'abord ici: support.microsoft.com/kb/316005

OriginalL'auteur Shiva Kumar | 2014-01-27

  1. 26

    Essayez ceci:

    DECLARE @input XML = '<dataset> 
 <metadata>
  <item name="NAME_LAST" type="xs:string" length="62" /> 
  <item name="NAME_FIRST" type="xs:string" length="62" /> 
  <item name="NAME_MIDDLE" type="xs:string" length="32" />
 </metadata>
<data>
<row>
 <value>SMITH</value> 
 <value>MARY</value> 
 <value>N</value> 
</row>
<row>
 <value>SMITH2</value> 
 <value>MARY2</value> 
 <value>N2</value> 
</row>
</data>
</dataset>'

INSERT INTO dbo.YourTable(ColName, ColFirstName, ColOther)
   SELECT
      Name = XCol.value('(value)[1]','varchar(25)'),
      FirstName = XCol.value('(value)[2]','varchar(25)'),
      OtherValue = XCol.value('(value)[3]','varchar(25)')
   FROM 
      @input.nodes('/dataset/data/row') AS XTbl(XCol)

    OriginalL'auteur marc_s

  2. 4
    Insert XML Data into sql Server table

Declare @retValue1 varchar(50);
Declare @XmlStr XML;
SET @XmlStr='<Customers>
 <customer>
    <ID>111589</ID>
    <FirstName>name1</FirstName>
    <LastName>Lname1</LastName>
    <Company>ABC</Company>
  </customer>
  <customer>
    <ID>12345</ID>
    <FirstName>name2</FirstName>
    <LastName>Lname2</LastName>
    <Company>ABC</Company>
  </customer>
  <customer>
    <ID>14567</ID>
    <FirstName>name3</FirstName>
    <LastName>Lname3</LastName>
    <Company>DEF</Company>
  </customer>
</Customers>';

@retValue='Failed';

INSERT INTO  [test_xmlinsert](
[id],
[firstName],
[lastName],
[company]
)
SELECT
COALESCE([Table].[Column].value('ID[1]', 'int'),0) as 'ID',
[Table].[Column].value('FirstName [1]', 'varchar(50)') as ' FirstName ',
[Table].[Column].value(' LastName[1]', 'varchar(50)') as ' LastName',
[Table].[Column].value(' Company [1]', 'varchar(50)') as ' Company'
 FROM @xmlData.nodes('/Customers /customer') as [Table]([Column])
IF(@@ROWCOUNT > 0 )
  SET @retValue='SUCCESS';
    avez-vous essayé d'exécuter le code?
    J'ai peut sembler idiot, mais d'abord à la déclaration de la variable et de définir la valeur par la suite m'a aidé.

    OriginalL'auteur kavitha Reddy