Looking for a good Bulk Insert XML Shredding example for SQL 2005

Question

A little help needed. I'm receiving an xml file similar to this:

<?xml version="1.0" encoding="utf-16"?>
<dc:GRANTEE xsi:schemaLocation="http://www.blahblahblah.com/FullSchema test.xsd " xmlns:dc="http://www.blahblahblah.com/FullSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
    <RPGID>90CU0024</RPGID>
    <PLANID>01</PLANID>
    <CASE>
        <CASEID>100001</CASEID>
        <RPGID>90CU0024</RPGID>
        <FILE_O>2008-02-08T00:00:00</FILE_O>
        <ADULT>
            <ADULTID>100001A1</ADULTID>
            <CASEID>100001</CASEID>
            <APRIMARY>1</APRIMARY>
            <ARLTNSHP>BM</ARLTNSHP>
            <ADOB>1978-12-03T00:00:00</ADOB>
            <ARACAI>1</ARACAI>
            <ASEX>2</ASEX>
            <SATX>
                <SATXID>MD2120378</SATXID>
                <ADULTID>100001A1</ADULTID>
                <SAASSESS>2008-02-22T00:00:00</SAASSESS>
                <PUBPRVTX>1</PUBPRVTX>
                <TXADMIT>2008-02-23T00:00:00</TXADMIT>
                <TXSET>5</TXSET>
            </SATX>
        </ADULT>
        <CHILD>
            <CHILDID>100001C1</CHILDID>
            <CASEID>100001</CASEID>
            <CINDEX>1</CINDEX>
            <CHBDATE>2008-02-05T00:00:00</CHBDATE>
            <CHSEX>1</CHSEX>
            <CHRACAI>0</CHRACAI>
            <MALTX>
                <MALTXID>10000023</MALTXID>
                <CHILDID>100001C1</CHILDID>
                <RPTDT>2008-02-05T00:00:00</RPTDT>
                <CHMAL1>2</CHMAL1>
             </MALTX>
         </CHILD>
    </CASE>

What I need to do is import it and shred it to tables with a stored proc. I've been unable to find anything other than general regurgitation of the examples in the books online, uh, online. what I need is a little syntax walk-through that shows how I can bulk insert the file (using OPENROWSET?) and then take all of the data and split it to matching tables while maintaining keys. It seems like it should be easy, but the reference material is just hard to come by. I also have access to schema if I need that and can annotate as well.

Anybody have a good reference?

Answer 1

You can use the BULK INSERT statement with the RAW keyword to load the data into a table with a single column. It will put the entire document into a single row. Then query the table and put the data into a variable with a datatype of XML.

This blog post shows how to get data out of the XML document.

Answer 2

Do you have multiple such xmls coming as a column in a table or do you have a single xml. Based on that I can tell you the startergy

Cheers Vinod Database Cosmos