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
