CODEWITHSUNDEEP
sqlsql-serverxmldatabase
Andi
Andi

Reputation: 13

XML into SQL Server several elements with same name into different columns

A machine produces xml files after testing. The problem is that the elements are all named the same but I need them to be in different columns.

Here is what the XML looks like:

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

From this XML i need the Name to be the column name and the Values to be in that column.

With my code:

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

Im only able to get all the values in 1 column because they all got the same path.

Is there any solution to manage this problem?

Thanks for your help!

Upvotes: 1

Views: 553

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

First of all: Do not use FROM OPENXML, this is outdated.

You can read your data as key-value-pairs in a derived table

This table can be pivoted for known column names. If you do not knwo (all) column names in advance, you might create the statement dynamically.

Try this:

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;

The result

Continental_Part_No Kendrion_Part_No    Prodoction_Date Counting_No
A2C73661103         4191506A00-O        20170222        0068

UPDATE Read the XML from file

Try it like this

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;

Upvotes: 1

Related Questions