如何将 xml 数据插入 SQL Server 表中? [英] How to Insert xml data into SQL Server table?

问题描述

如何将以下 XML 数据导入三列 SQL Server 表?

How to import below XML data into SQL Server table with three columns?

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

推荐答案

试试这个:

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)

这篇关于如何将 xml 数据插入 SQL Server 表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！