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 procCREATE 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 !!!!
