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

Question

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!

Gottfried Lesigang · Accepted Answer · 2017-03-01 15:41:34Z

1

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;

edited Mar 1, 2017 at 15:41

answered Feb 28, 2017 at 13:54

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Andi Over a year ago

I searched for xml to ms sql on google almost everytime i found it with FROM OpenXML. Next thing is that i need to do this for all upcoming XMLs in the Future as an automatic job. So to just declare that XML Code doesnt work really. It wasnt the whole File aswell there are about 40.000 rows more.

Gottfried Lesigang Over a year ago

@Andi This article is more than 8 years old...

Gottfried Lesigang Over a year ago

@Andi, The one and only situation where FROM OPENXML might still have its place is, when you have to read various values and subsets out of the same rather large XML. In this case the preparation is done once and following sub-calls will work rather fast.

Andi Over a year ago

are there any types of tutorials for this?

Gottfried Lesigang Over a year ago

@Andi just search for sql server xml tutorial and you will find many. Another good source might be to read some related answers here on SO. It could be a good start to visit the profile of a guy called Shnugo :-)

|

Collectives™ on Stack Overflow

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

1 Answer 1

UPDATE Read the XML from file

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE Read the XML from file

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related