2

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">
    <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?

Kevin Fairchild
  • 10,891
  • 6
  • 33
  • 52
mrankin
  • 2,373
  • 3
  • 25
  • 29

1 Answers1

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.

mrdenny
  • 4,990
  • 2
  • 21
  • 28
  • Just a note for future readers that the `sp_xml_preparedocument` method shown in that link is obsolete. It loads the entire xml into memory and you should be aware of its limitations. Instead, you can query xml data types natively. `Openxml` is also not suitable for large sized xml data. See [this example](http://stackoverflow.com/questions/688015/importing-xml-into-sql-server/688790) . – Amit Naidu Jun 11 '13 at 03:47