Importing Data from XML file to tables.

hi All ,

I have two tables as Table1([ColA] , [ColB]) , Table2([Col1] , [Col2]) in my DB.

And i have a single XML file which contains the data for the two tables separately.

as <Table1> +

<Table2> +

Now i have to import this data from the XML to both the Tables in one shot. Plz suggest a possible way to get this done.

Thanks in advance for the help.

you can try this proc

CREATE PROCEDURE dbo.SaveXML
(
@.XMLDoc text
)
AS
DECLARE @.idoc int
DECLARE @.SQLStatement as nvarchar(4000)
SET NOCOUNT ON

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.XMLDoc

DECLARE @.cTableName varchar(50)
DECLARE @.cXMLNode varchar(50)

DECLARE TableNamesCursor CURSOR FOR
SELECT TableName
FROM (
SELECT 'Table1' AS TableName UNION ALL
SELECT 'Table2' AS TableName
) TableNames

OPEN TableNamesCursor

FETCH NEXT FROM TableNamesCursor
INTO @.cTableName

SET @.SQLStatement = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.cXMLNode = '/' + @.cTableName

SET @.SQLStatement = @.SQLStatement + N' SELECT * INTO #tmp' + @.cTableName + ' FROM OPENXML(@.idoc,''' + @.cXMLNode + ''', 2)
WITH ' + @.cTableName + ';
INSERT INTO ' + @.cTableName + ' SELECT * FROM #tmp' + @.cTableName + ''

FETCH NEXT FROM TableNamesCursor
INTO @.cTableName
END

CLOSE TableNamesCursor
DEALLOCATE TableNamesCursor

EXECUTE sp_executesql @.SQLStatement, N'@.XMLDoc text,@.idoc int',@.XMLDoc, @.idoc

EXEC sp_xml_removedocument @.idoc

SET NOCOUNT OFF
GO|||

thanks for the quick reply Rhamille .

As in ur proc u r passing the XML as parameter.

But my prob is i have xml file saved at location c:\MyXML.xml .

How can i retrieve the required XML contents from this saved file .

Also the table names in DB are Table1 & Table2 . And the format of the XML file is something like this :

-<Report .......... >

- <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 the necessary changes in the SP so that this can be achived.

|||

Here it is...(i used your xml as sample xml file)

Code Snippet

Create Table #Content

(

Lines Nvarchar(4000)

);

Insert Into #Content

Exec master..xp_cmdshell 'Type C:\data.xml' --Change the XML File Path

Declare @.xmlas NVarchar(4000);

Declare @.idoc as Int;

Set @.xml = ''

Select @.xml = @.xml + Ltrim(Rtrim(Lines)) From #Content Where Lines is NOT NULL

Select @.xml

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml

--First table

Select

*

From

OpenXML(@.idoc, '/Report/table1/Data/Record',1)

With

(

col1 varchar(100),

col2 varchar(100)

);

--Second table

Select

*

From

OpenXML(@.idoc, '/Report/table2/Data/Record',1)

With

(

col1 varchar(100),

col2 varchar(100)

);

EXEC sp_xml_removedocument @.idoc

|||here's the updated sp from my previous post

CREATE PROCEDURE dbo.SaveXML
(
@.xmlPath nvarchar(100)
)
AS

DECLARE @.idoc int
DECLARE @.SQLStatement as nvarchar(4000)
DECLARE @.XMLDoc nvarchar(4000)

SET NOCOUNT ON

CREATE TABLE #XMLTemp (rowID int IDENTITY, xmlRow nvarchar(4000))
DECLARE @.cmd nvarchar(100)
SET @.cmd = N'TYPE ' + CONVERT(nvarchar,@.xmlPath)
INSERT #XMLTemp EXEC master.dbo.xp_cmdshell @.cmd
SELECT @.XMLDoc =
CASE WHEN rowID = 1 THEN
ISNULL(RTRIM(xmlRow), '')
ELSE
@.XMLDoc + ISNULL(RTRIM(xmlRow), '')
END
FROM #XMLTemp
WHERE xmlRow IS NOT NULL
ORDER BY
rowID ASC

DROP TABLE #XMLTemp

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.XMLDoc

DECLARE @.cTableName varchar(50)
DECLARE @.cXMLNode varchar(50)

DECLARE TableNamesCursor CURSOR FOR
SELECT DestinationTables
FROM (
SELECT 'Table1' AS DestinationTables UNION ALL
SELECT 'Table2' AS DestinationTables
) XMLDumpTables

OPEN TableNamesCursor

FETCH NEXT FROM TableNamesCursor
INTO @.cTableName

SET @.SQLStatement = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.cXMLNode = '/Report/' + @.cTableName + '/Data/Record'

SET @.SQLStatement = @.SQLStatement + N' SELECT * INTO #tmp' + @.cTableName + ' FROM OPENXML(@.idoc,''' + @.cXMLNode + ''', 1)
WITH ' + @.cTableName + ';
INSERT INTO ' + @.cTableName + ' SELECT * FROM #tmp' + @.cTableName + ''

FETCH NEXT FROM TableNamesCursor
INTO @.cTableName
END

CLOSE TableNamesCursor
DEALLOCATE TableNamesCursor

EXECUTE sp_executesql @.SQLStatement, N'@.XMLDoc text,@.idoc int',@.XMLDoc, @.idoc

EXEC sp_xml_removedocument @.idoc

SET NOCOUNT OFF
GO

-- to use EXEC dbo.SaveXML 'C:\data.xml'|||

If you're using SQL Server 2005

Code Snippet

CREATE PROCEDURE dbo.LoadXML

AS

SET NOCOUNT ON

DECLARE @.xdoc xml

SELECT @.xdoc = bulkcolumn

from openrowset(bulk 'c:\myxml.xml', SINGLE_BLOB) as xdata --Your xml input file

insert into Table1

select t1.row.value('@.col1', 'char') as ColA, --Change 'char' to your datatype for each column

t1.row.value('@.col2', 'char') as ColB

from @.xdoc.nodes('/Report/table1/Data/Record') as t1(row)

insert into Table2

select t2.row.value('@.col1', 'char') as Col1,

t2.row.value('@.col2', 'char') as Col2

from @.xdoc.nodes('/Report/table2/Data/Record') as t2(row)

|||

Thanks Rhamille and Manivannan ( Mani ) for ur guidance..

Mission Accomplished !!!!

|||Thanks DaleJ for a new idea...i ll try this one too...