Extract XML data and load into database (possible approaches)

Question

My counterpart provided us a sample XML file and we are doing proof of concept whether the data in the file can be imported into a database.

The XML file is shown as follows:

<?xml version="1.0" encoding="UTF-8" ?>
<xmloutfile>
<header>
<TYPE>XLFT</TYPE>
<DN>C001</DN>
</header>
<table>
<row>
<column></column>
<column></column>
<column></column>
<column></column>
<column></column></row>
<row>
<column></column>
<column></column>
<column></column>
<column></column>
<column></column></row>
<row>
<column>ID</column>
<column>Title</column>
<column>Type</column>
<column>Country</column>
<column></column></row>
<row>
<column>12345</column>
<column>ABC</column>
<column>SN</column>
<column>Hong Kong</column>
<column></column></row>
<row>
<column>17777</column>
<column></column>
<column>MO</column>
<column>China</column>
<column></column></row>
<row>
<column></column>
<column></column>
<column></column>
<column></column>
<column></column></row></table></xmloutfile>

What we are interested with is the values of ID, Title, Type, Country (and perhaps a remark)

<column>ID</column>
<column>Title</column>
<column>Type</column>
<column>Country</column>
<column></column></row>
<row>
<column>12345</column>
<column>ABC</column>
<column>SN</column>
<column>Hong Kong</column>
<column></column></row>

just imagine this is a general table, how could I insert those data accordingly?

Eventually I would like to do it using SSIS but at this moment (it would be good if I can do it via SSIS!), but just want to evaluate any feasible approaches. Tks

In other words, you want to remove all empty rows/columns?

Hari Prasad
– Hari Prasad

2016-06-14 03:32:38 +00:00
Commented Jun 14, 2016 at 3:32 — Hari Prasad
– Hari Prasad, Commented Jun 14, 2016 at 3:32

Kirill Polishchuk · Accepted Answer · 2016-06-14 03:21:56Z

1

XQuery:

declare @x xml ='<xmloutfile>
  <header>
    <TYPE>XLFT</TYPE>
    <DN>C001</DN>
  </header>
  <table>
    <row>
      <column></column>
      <column></column>
      <column></column>
      <column></column>
      <column></column>
    </row>
    <row>
      <column></column>
      <column></column>
      <column></column>
      <column></column>
      <column></column>
    </row>
    <row>
      <column>ID</column>
      <column>Title</column>
      <column>Type</column>
      <column>Country</column>
      <column></column>
    </row>
    <row>
      <column>12345</column>
      <column>ABC</column>
      <column>SN</column>
      <column>Hong Kong</column>
      <column></column>
    </row>
    <row>
      <column>17777</column>
      <column></column>
      <column>MO</column>
      <column>China</column>
      <column></column>
    </row>
    <row>
      <column></column>
      <column></column>
      <column></column>
      <column></column>
      <column></column>
    </row>
  </table>
</xmloutfile>'


select t.c.value('column[1]', 'nvarchar(max)')
    , t.c.value('column[2]', 'nvarchar(max)')
    , t.c.value('column[3]', 'nvarchar(max)')
    , t.c.value('column[4]', 'nvarchar(max)')
    , t.c.value('column[5]', 'nvarchar(max)')
from @x.nodes('//row') t(c)

answered Jun 14, 2016 at 3:21

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

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

1 Comment

Trowa Over a year ago

Great tks! I used : select t.c.value('column[1]', 'nvarchar(max)') ID , t.c.value('column[2]', 'nvarchar(max)') Title , t.c.value('column[3]', 'nvarchar(max)') Type , t.c.value('column[4]', 'nvarchar(max)') Country , t.c.value('column[5]', 'nvarchar(max)') Remark from @XML.nodes('//row') t(c) where c.value('./column[1]','varchar(max)') not in ( 'ID', '')

Collectives™ on Stack Overflow

Extract XML data and load into database (possible approaches)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related