CODEWITHSUNDEEP
sqlsql-serverxml
no_event_logged
no_event_logged

Reputation: 59

Importing XML to SQL with one element coppied to multiple rows

Lets say I have many XML files to import into a new SQL database, each XML file has a varied number of <book> branched that will be imported to a new row (I have mostly worked that bit out thanks to this) but each XML file also has a single <country> element that is common for all <book>'s in that XML file.

How would read each *.xml file from a folder and get it to import like so:

test1.xml

<store>
  <bookstore>
    <book>
      <ref_title>
        <title>Harry Potter</title>
      </ref_title>
      <ref_author>
       <author>J K. Rowling</author>
      </ref_author>
    </book>
    <book>
      <ref_title>
        <title>Petes Book of Pie</title>
      </ref_title>
      <ref_author>
       <author>Pete P</author>
      </ref_author>
    </book>
  </bookstore>
  ...
  <countrycode>
    <country>Australia</country>
  </countrycode>
</store>

Test2.xml

<store>
  <bookstore>
    <book>
      <ref_title>
        <title>Gotta Go Fast</title>
      </ref_title>
      <ref_author>
       <author>Dr Speed</author>
      </ref_author>
    </book>
    <book>
      <ref_title>
        <title>If It Aint Broke</title>
      </ref_title>
      <ref_author>
       <author>Mr fixit</author>
      </ref_author>
    </book>
  </bookstore>
  ...
  <countrycode>
    <country>USA</country>
  </countrycode>
</store>

SQL output:

TITLE               AUTHOR          COUNTRY
Harry Potter        J K. Rowling    Australia
Petes Book of Pie   Pete P          Australia
Gotta Go Fast       Dr Speed        USA
If It Aint Broke    Mr fixit        USA

Thank you again for your help!

Upvotes: 0

Views: 56

Answers (1)

critical_error
critical_error

Reputation: 6706

Here's an example that should get you started. You can run this in SSMS.

-- Fetch data from files...
DECLARE @Data VARCHAR(MAX);

    SELECT @Data = BulkColumn
    FROM OPENROWSET ( BULK 'E:\Temp\test1.xml', SINGLE_CLOB ) AS x;

    SELECT @Data = @Data + BulkColumn
    FROM OPENROWSET ( BULK 'E:\Temp\test2.xml', SINGLE_CLOB ) AS x;

-- Set @Data into an XML variable with a "root" element.
DECLARE @xml XML = CAST( '<root>' + @Data + '</root>' AS XML );

-- Return the XML results.
SELECT
    doc.fld.value ( 'data(ref_title/title)[1]', 'VARCHAR(255)' ) AS [TITLE],
    doc.fld.value ( 'data(ref_author/author)[1]', 'VARCHAR(255)' ) AS [AUTHOR],
    doc.fld.value ( 'data(../../countrycode/country)[1]', 'VARCHAR(255)' ) AS [COUNTRY]
FROM @xml.nodes( '//root/store/bookstore/book' ) doc ( fld );

RETURNS

+-------------------+--------------+-----------+
|       TITLE       |    AUTHOR    |  COUNTRY  |
+-------------------+--------------+-----------+
| Harry Potter      | J K. Rowling | Australia |
| Petes Book of Pie | Pete P       | Australia |
| Gotta Go Fast     | Dr Speed     | USA       |
| If It Aint Broke  | Mr fixit     | USA       |
+-------------------+--------------+-----------+

NOTE: In order to use OPENROWSET/BULK in this manner, the files (in this case test1.xml and test2.xml) must exist on the server where SQL is running and SQL must have access to their location.

It's also worth noting that this example implements no error handing in the event the XML is badly formatted or a file is empty. It's just meant as a reference to get you started.

Contents of test1.xml

<store>
  <bookstore>
    <book>
      <ref_title>
        <title>Harry Potter</title>
      </ref_title>
      <ref_author>
       <author>J K. Rowling</author>
      </ref_author>
    </book>
    <book>
      <ref_title>
        <title>Petes Book of Pie</title>
      </ref_title>
      <ref_author>
       <author>Pete P</author>
      </ref_author>
    </book>
  </bookstore>
  <countrycode>
    <country>Australia</country>
  </countrycode>
</store>

Contents of test2.xml

<store>
  <bookstore>
    <book>
      <ref_title>
        <title>Gotta Go Fast</title>
      </ref_title>
      <ref_author>
       <author>Dr Speed</author>
      </ref_author>
    </book>
    <book>
      <ref_title>
        <title>If It Aint Broke</title>
      </ref_title>
      <ref_author>
       <author>Mr fixit</author>
      </ref_author>
    </book>
  </bookstore>
  <countrycode>
    <country>USA</country>
  </countrycode>
</store>

Upvotes: 1

Related Questions