sql - 将 XML 导入 SQL 表并格式化数据

Question

有人能帮我吗 我正在尝试将 xml 文件导入到 sql server 表中。我可以导入所需的数据，但在获取所需格式时遇到一些问题。

    declare @input XML = '<Sub>
    <Results>
    <Result>
      <ids>
        <id>
          <type>code</type>
          <value>9004a3d2</value>
        </id>
        <id>
          <type>username</type>
          <value>jbloggs001</value>
          <date>20160725</date>
        </id>
        <id>
         <type>EmployeeID</type>
         <value>01234</value>
         <date>20160725</date>
        </id>
     </ids>
    </Result>
</Results>
</Sub>'
   SELECT
     datatype = XCol.value('(type)[1]','varchar(25)'),
    datavalue = XCol.value('(value)[1]','varchar(50)')
   FROM 
      @input.nodes('/Sub/Results/Result/ids/id') AS XTbl(XCol)

这给出了 3 列，例如:

datatype       datavalue
--------------------------------
   code        9004a3d2   
   username    jbloggs001
   employeeID  01234

是否可以将其导入为？

EmployeeID   USername     Code
    ---------------------------------
   01234        jbloggs    0019004a3d2

谢谢

Answer 1

正如评论中所建议的那样，可以使用 PIVOT 完成:

   SELECT *
   FROM (
   SELECT
     datatype = XCol.value('(type)[1]','varchar(25)'),
    datavalue = XCol.value('(value)[1]','varchar(50)')
   FROM 
      @input.nodes('/Sub/Results/Result/ids/id') AS XTbl(XCol)
    ) as p
    PIVOT (
        MAX(datavalue) FOR datatype IN (EmployeeID,username,code)
    ) as pvt

输出:

EmployeeID  username    code
01234       jbloggs001  9004a3d2

如果输入总是具有相同的类型:

SELECT  XCol.value('(id/value)[3]','varchar(50)') as EmployeeID,
        XCol.value('(id/value)[2]','varchar(50)') as username,
        XCol.value('(id/value)[1]','varchar(50)') as code
FROM @input.nodes('/Sub/Results/Result/ids') AS XTbl(XCol)