sql - XML 到 SQL Server 中的几个具有相同名称的元素到不同的列中

Question

一台机器测试后生成xml文件。问题是所有元素的名称都相同，但我需要它们位于不同的列中。

这是 XML 的样子:

<WorkProcess xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Header>
    <Element>
      <Name>
        <string>CONTINENTAL_PART_NO</string>
      </Name>
      <Content>
        <Header-Item>
          <Name>Continental_Part_No</Name>
          <Value>A2C73661103</Value>
          <Comment />
        </Header-Item>
      </Content>
    </Element>
    <Element>
      <Name>
        <string>KENDRION_PART_NO</string>
      </Name>
      <Content>
        <Header-Item>
          <Name>Kendrion_Part_No</Name>
          <Value>4191506A00-O</Value>
          <Comment />
        </Header-Item>
      </Content>
    </Element>
    <Element>
      <Name>
        <string>PRODOCTION_DATE</string>
      </Name>
      <Content>
        <Header-Item>
          <Name>Prodoction_Date</Name>
          <Value>20170222</Value>
          <Comment />
        </Header-Item>
      </Content>
    </Element>
    <Element>
      <Name>
        <string>COUNTING_NO</string>
      </Name>
      <Content>
        <Header-Item>
          <Name>Counting_No</Name>
          <Value>0068</Value>
          <Comment>Count of IO-Parts</Comment>
        </Header-Item>
      </Content>
    </Element>

从这个 XML 中，我需要将名称作为列名，并将值作为该列中的值。

用我的代码:

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT *
FROM OPENXML(@hDoc, 'WorkProcess/Header/Element/Content/Header-Item',2)
WITH 
    (Continental_Part_No [varchar](50) 'Value')

EXEC sp_xml_removedocument @hDoc
GO

我只能获取 1 列中的所有值，因为它们都有相同的路径。

有什么解决方案可以解决这个问题吗？

感谢您的帮助!

Answer 1

首先:不要使用FROM OPENXML，这已经过时了。

您可以在派生表中以键值对的形式读取数据

此表可以针对已知的列名称进行透视。如果您事先不知道(所有)列名称，则可以动态创建语句。

试试这个:

DECLARE @xml XML=
N'<WorkProcess xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Header>
    <Element>
      <Name>
        <string>CONTINENTAL_PART_NO</string>
      </Name>
      <Content>
        <Header-Item>
          <Name>Continental_Part_No</Name>
          <Value>A2C73661103</Value>
          <Comment />
        </Header-Item>
      </Content>
    </Element>
    <Element>
      <Name>
        <string>KENDRION_PART_NO</string>
      </Name>
      <Content>
        <Header-Item>
          <Name>Kendrion_Part_No</Name>
          <Value>4191506A00-O</Value>
          <Comment />
        </Header-Item>
      </Content>
    </Element>
    <Element>
      <Name>
        <string>PRODOCTION_DATE</string>
      </Name>
      <Content>
        <Header-Item>
          <Name>Prodoction_Date</Name>
          <Value>20170222</Value>
          <Comment />
        </Header-Item>
      </Content>
    </Element>
    <Element>
      <Name>
        <string>COUNTING_NO</string>
      </Name>
      <Content>
        <Header-Item>
          <Name>Counting_No</Name>
          <Value>0068</Value>
          <Comment>Count of IO-Parts</Comment>
        </Header-Item>
      </Content>
    </Element>
  </Header>
</WorkProcess>';

SELECT p.*
FROM
(
    SELECT e.value(N'(Content/Header-Item/Name/text())[1]','nvarchar(max)') AS ColumnName
          ,e.value(N'(Content/Header-Item/Value/text())[1]','nvarchar(max)') AS ColumnValue
    FROM @xml.nodes(N'/WorkProcess/Header/Element') AS A(e)
) AS t
PIVOT
(
    MIN(ColumnValue) FOR ColumnName IN(Continental_Part_No
                                      ,Kendrion_Part_No
                                      ,Prodoction_Date
                                      ,Counting_No)
) AS p;

结果

Continental_Part_No Kendrion_Part_No    Prodoction_Date Counting_No
A2C73661103         4191506A00-O        20170222        0068

UPDATE 从文件中读取 XML

这样试试

WITH MyXmlFile(TheFile) AS
(
    SELECT CAST(BulkColumn AS XML) FROM OPENROWSET(BULK 'C:\YourPath\YourFile.xml', SINGLE_BLOB ) a
)
SELECT p.*
FROM
(
    SELECT e.value(N'(Content/Header-Item/Name/text())[1]','nvarchar(max)') AS ColumnName
          ,e.value(N'(Content/Header-Item/Value/text())[1]','nvarchar(max)') AS ColumnValue
    FROM MyXmlFile
    CROSS APPLY MyXmlFile.TheFile.nodes(N'/WorkProcess/Header/Element') AS A(e)
) AS t
PIVOT
(
    MIN(ColumnValue) FOR ColumnName IN(Continental_Part_No
                                      ,Kendrion_Part_No
                                      ,Prodoction_Date
                                      ,Counting_No)
) AS p;