sql - XML 导入 SQL Server 的噩梦

Question

我正在尝试将 XML 文件中的数据加载到 SQL Server 中，并且在添加相关键（存储在“键”节点中）以将 XML 数据链接到 SQL Server 中时遇到问题。

请考虑下面的 XML 片段。

<person>
    <keys>
        <key>xx8546</key>
        <key>yy369</key>
    </keys>

    <sex>f</sex>

   <names>
      <name>
         <prename>Sarah</prename>
         <surname>Connor</surname>
      </name>

      <name>
         <prename>Sarah</prename>
         <surname>Williams</surname>
      </name>
  </names>

  <mmx>
      <spouse-name>
          <prename>John</prename>
          <surname>Wyatt</surname>
      </spouse-name>
      <year-of-marriage>1985</year-of-marriage>
  </mmx>

  <pbx>
     <spouse-name>
        <prename>John</prename>
        <surname>Williams</surname>
     </spouse-name>

     <child-name>
        <prename>Sarah</prename>
        <surname>Bean</surname>
     </child-name>

     <year-of-baptism>2007</year-of-baptism>
 </pbx>
</person>  

我需要将相关密钥添加到每个节点。

这是我的 tsql 的一部分：

DECLARE @x xml;
DECLARE @hdoc int;

SELECT @x = P
FROM OPENROWSET (BULK 'C:\person.xml', SINGLE_BLOB) AS Person(P)

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

--Person
SELECT * --into tbl_Person
FROM OPENXML (@hdoc, '/persons/person/names/name', 2)
WITH (
[key]  nvarchar(100)  '../../keys/key', 
prename varchar(100),
surname varchar(100),
sex varchar(50) '../../sex'
)

上面的代码只将第一个键值插入到两个结果中，例如<key xx8546 </key>。请你能帮我弄清楚这真的很令人沮丧。

非常感谢，

国阵。

标签： sqlsql-serverxmlxslt

Answer 1

我认为您不能同时导入所有数据，因为<keys>没有明确链接到其他数据。

您必须在数据之间创建连接：例如，您可以创建包含数据的表并添加标识列，然后在标识列上查询连接它们的表。

这是这种方法的一个基本示例：

create table tb_keys (id int identity(1,1), keycode nvarchar(50))
create table tb_names (id int identity(1,1), prename nvarchar(500), surname nvarchar(500))

declare @x xml;

select @x = p from openrowset (bulk 'c:\person.xml', single_blob) as person(p)

insert into tb_keys
select p.value('.', 'nvarchar(50)') as [key] from @x.nodes('/person/keys/key') AS T(p)

insert into tb_names
select 
    x.rec.query('./prename').value('.', 'nvarchar(500)') as 'prename',
    x.rec.query('./surname').value('.', 'nvarchar(500)') as 'surname'
from @x.nodes('/person//names/name') as x(rec)

select k.keycode, n.prename, n.surname 
from tb_keys k inner join tb_names n on k.id = n.id

输出：