Dumping Data from a XML file to DB Tables

Hi All ,

I have an XML file saved in my machine at c:\MyXML.xml . I have two tables in my DB as Table1 ( [col1],[col2]) & Table2()[col1],[col2] . I have to dump the data from this xml file to these two tables in one shot .The format of the XML file MyXML.xml is something like this :

<?xml version = "1.0">

-<Report xmlns = " MyReport".......... >

- <table1>

- <Data>

<Record col1 = "A" col2 = "B" / >

<Record col1 = "C" col2 = "D" / >

<Record col1 = "E" col2 = "F" / >

</Data>

</table1>

- <table2>

- <Data>

<Record col1 = "A" col2 = "B" / >

<Record col1 = "C" col2 = "D" / >

<Record col1 = "E" col2 = "F" / >

</Data>

</table2>

</Report>

Plz suggest me some possible ways to get this done . I m using sql 2005.

Thanks in advance for the help.

Here is a complete example, you can use the OPENXML statement after the stored procedure sp_xml_preparedocument has parsed the XML:

Code Snippet

CREATE TABLE table1 (

col1 nvarchar(10),

col2 nvarchar(10)

);

CREATE TABLE table2 (

col1 nvarchar(10),

col2 nvarchar(10)

);

DECLARE @.xmlDocument xml;

SET @.xmlDocument = '<Report xmlns="MyReport">

<table1>

<Data>

<Record col1 = "A" col2 = "B" />

<Record col1 = "C" col2 = "D" />

<Record col1 = "E" col2 = "F" />

</Data>

</table1>

<table2>

<Data>

<Record col1 = "A" col2 = "B" />

<Record col1 = "C" col2 = "D" />

<Record col1 = "E" col2 = "F" />

</Data>

</table2>

</Report>';

DECLARE @.docHandle int;

EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument, N'<Report xmlns:ns1="MyReport"/>';

INSERT INTO table1

SELECT *

FROM OPENXML (@.docHandle, N'/ns1:Report/ns1:table1/ns1:Data/ns1:Record', 0)

WITH table1;

INSERT INTO table2

SELECT *

FROM OPENXML (@.docHandle, N'/ns1:Report/ns1:table2/ns1:Data/ns1:Record', 0)

WITH table2;

EXEC sp_xml_removedocument @.docHandle;

SELECT col1, col2 FROM table1;

SELECT col1, col2 FROM table2