Pivot XML into SQL Columns & values

Question

I have an XML file in the following format. Not every field name will have a value. Each field except for the id field will be varchar(40).

<index>
  <doc id="0">
    <field name="MFG">
      <val>ACME</val>
    </field>
    <field name="InternalCode">
      <val />
    </field>
    <field name="partnumber">
      <val>012345-00</val>
    </field>
    <field name="partdescription">
      <val>PIN</val>
    </field>
  </doc>
  <doc id="1">
    <field name="MFG">
      <val />
    </field>
    <field name="InternalCode">
      <val>ABCDE</val>
    </field>
    <field name="partnumber">
      <val>919-555-7Z</val>
    </field>    
    <field name="partdescription">
      <val>WASHER</val>
    </field>
  </doc>
  <doc id="2">
    <field name="MFG">
      <val>YOUR COMPANY</val>
    </field>
    <field name="InternalCode">
      <val />
    </field>
    <field name="partnumber">
      <val>131415</val>
    </field>
    <field name="partdescription">
      <val>BOLT</val>
    </field>
  </doc>
</index>

What I would like to do is to read the XML & populate a table in SQL in the following manner.

In other words, after the rowid, pivot the rest of the attributes as columns and their values as the column value. I'm using the following code that will list the rowid, attribute & their values as rows.

SELECT  XMLAttribute.rowid, XMLAttribute.name, XMLAttribute.val
FROM OPENXML (@hdoc, 'index/doc/field', 2 ) 
        WITH (rowid int '../@id',
        name       VARCHAR(128) '@name',
        val varchar(128) 'val'                  
            ) AS XMLAttribute

Can this (pivot after the rowid) be done? If so, how?

TT. · Accepted Answer · 2016-02-24 21:25:11Z

You can better do this with XPath/XQuery than with OPENXML. Check out documentation on XML.nodes() and XML.value(). Check out some XPath guide online, this is a good one.

DECLARE @i XML=
'<index>
<doc id="0"><field name="MFG"><val>ACME</val></field><field name="InternalCode"><val /></field><field name="partnumber"><val>012345-00</val></field><field name="partdescription"><val>PIN</val></field></doc>
<doc id="1"><field name="MFG"><val /></field><field name="InternalCode"><val>ABCDE</val></field><field name="partnumber"><val>919-555-7Z</val></field><field name="partdescription"><val>WASHER</val></field></doc>
<doc id="2"><field name="MFG"><val>YOUR COMPANY</val></field><field name="InternalCode"><val /></field><field name="partnumber"><val>131415</val></field><field name="partdescription"><val>BOLT</val></field></doc>
</index>';

SELECT
    rowid=n.v.value('@id','VARCHAR(40)'),
    MFG=n.v.value('(field[@name="MFG"]/val)[1]','VARCHAR(40)'),
    InternalCode=n.v.value('(field[@name="InternalCode"]/val)[1]','VARCHAR(40)'),
    partnumber=n.v.value('(field[@name="partnumber"]/val)[1]','VARCHAR(40)'),
    partdescription=n.v.value('(field[@name="partdescription"]/val)[1]','VARCHAR(40)')
FROM
    @i.nodes('/index/doc') AS n(v);

Result:

+-------+--------------+--------------+------------+-----------------+
| rowid |     MFG      | InternalCode | partnumber | partdescription |
+-------+--------------+--------------+------------+-----------------+
|     0 | ACME         |              | 012345-00  | PIN             |
|     1 |              | ABCDE        | 919-555-7Z | WASHER          |
|     2 | YOUR COMPANY |              | 131415     | BOLT            |
+-------+--------------+--------------+------------+-----------------+

Collectives™ on Stack Overflow

Pivot XML into SQL Columns & values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related