Insert XML file to SQL Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert XML file to SQL Table

Author

Topic

vvailaya
Starting Member

2 Posts

Posted - 2012-01-07 : 12:19:50

Hi Sqlteam,
I have tried doing this with simple XML and it works! I am in a situation where a system throws a xml file ( I don’t have any control to change this format) and I need to insert the data in xml into a table.
Here’s the XML file data:(Units_XML_Test.xml)
---------------------
<?xml version="1.0" encoding="utf-8"?>
<RS xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" ><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:saw-sql="urn:saw-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset"><xsd:complexType name="R"><xsd:sequence><xsd:element name="C0" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:displayFormula=""- User"."Name"" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="User" saw-sql:columnHeading="End User Name" /><xsd:element name="C1" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:displayFormula=""- Country"."Country Code"" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Country" saw-sql:columnHeading="Country Code" /><xsd:element name="C2" type="xsd:double" minOccurs="0" maxOccurs="1" saw-sql:type="double" saw-sql:displayFormula=""- Fact"."Units"" saw-sql:aggregationRule="sum" saw-sql:aggregationType="agg" saw-sql:tableHeading="Fact" saw-sql:columnHeading="Units" /></xsd:sequence></xsd:complexType></xsd:schema><R><C0>Elite</C0><C1>NL</C1><C2>283</C2></R><R><C0>Interactive</C0><C1>US</C1><C2>69</C2></R><R><C0>Metall</C0><C1>CH</C1><C2>426</C2></R></RS>
---------------------

I tried inserting it in 2 ways (Both doesnt throw an error but data is not inserted into the table.
Here's the SQL Statements used to insert the XML data to table

Create table Unit_Test (
[End User Name] nvarchar(100),
[Country Code] nvarchar(2),
Units int
)

--Method 1
INSERT INTO Unit_Test ([End User Name], [Country Code], Units)
SELECT
X.OBI.query('C0').value('.', 'nvarchar(100)'),
X.OBI.query('C1').value('.', 'nvarchar(2)'),
X.OBI.query('C2').value('.', 'int')
FROM (SELECT CAST(x AS XML)FROM OPENROWSET(BULK 'C:\XML\Units_XML_Test.xml',SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY x.nodes('RS/R') AS X(OBI);
--(0 row(s) affected)

--Method 2

DECLARE @CD TABLE (XMLData XML);
INSERT INTO @CD SELECT * FROM OPENROWSET(BULK 'C:\XML\Units_XML_Test.xml', SINGLE_BLOB) rs;
INSERT INTO Unit_Test ([End User Name], [Country Code], Units)
SELECT [End User Name] = x.data.value('C0[1]','nvarchar(100)'),
[Country Code] = x.data.value('C1[1]','nvarchar(2)'),
Units = x.data.value('C2[1]','int')
FROM @CD t
CROSS APPLY t.XMLData.nodes('/RS/R') x(data);

--(1 row(s) affected)
--(0 row(s) affected)

Can you please help me out here.

Thanks & Regards,
Venkatesh

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-07 : 17:02:33

Is the XML you have posted the actual XML data? If I simply take the string you copied and try to cast it to an XML variable, it does not parse. I suspect if I tried to do the xml bulk insert I would get the same error (but I have not tried that).

vvailaya
Starting Member

2 Posts

Posted - 2012-01-08 : 08:10:12

Thanks Sunitha for your comments.

Yes, the XML that I have posted is the actula XML data.

I got this to work. Here's SQL Code for the same. what I was missing was to add NAMESPACES

------------------
;
WITH XMLNAMESPACES(DEFAULT 'urn:schemas-microsoft-com:xml-analysis:rowset')
INSERT INTO #Unit_Test ([End User Name], [Country Code], Units)
SELECT X.OBI.query('C0').value('.', 'nvarchar(100)') AS c0,
X.OBI.query('C1').value('.', 'nvarchar(2)')AS c1,
X.OBI.query('C2').value('.', 'int') AS c2
FROM (SELECT CAST(x AS XML)FROM OPENROWSET(BULK 'C:\XML\Units_XML_Test.xml',SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY x.nodes('RS/R') AS X(OBI);
------------------

But I am still stuck with this. Due to performance issues. I tried the similar stuff with additional columns, the same query format & XML format. It took 5hrs just to read 600 rows of data.

Is there another way to do this faster ?

I have read that if we load the data from the file into a table before shredding it and then replace the OPENROWSET in the query with that table reference will help.
I have no idea how to do this. Can you help me out to tune this SQL to get better performance ?

Here's new SQL Code which took 5hrs just to read 600 rows of data.
;
WITH XMLNAMESPACES
(DEFAULT 'urn:schemas-microsoft-com:xml-analysis:rowset')
SELECT
X.OBI.query('C0').value('.', 'nvarchar(50)') as C0,
X.OBI.query('C1').value('.', 'nvarchar(50)')as C1,
X.OBI.query('C2').value('.', 'nvarchar(500)')as C2,
X.OBI.query('C3').value('.', 'nvarchar(500)')as C3,
X.OBI.query('C4').value('.', 'nvarchar(500)')as C4,
X.OBI.query('C5').value('.', 'nvarchar(50)')as C5,
X.OBI.query('C6').value('.', 'nvarchar(50)')as C6,
X.OBI.query('C7').value('.', 'nvarchar(50)')as C7,
X.OBI.query('C8').value('.', 'nvarchar(50)')as C8,
X.OBI.query('C9').value('.', 'nvarchar(50)')as C9,
X.OBI.query('C10').value('.', 'nvarchar(50)')as C10,
X.OBI.query('C11').value('.', 'nvarchar(500)')as C11,
X.OBI.query('C12').value('.', 'nvarchar(50)')as C12,
X.OBI.query('C13').value('.', 'nvarchar(50)')as C13,
X.OBI.query('C14').value('.', 'nvarchar(50)')as C14,
X.OBI.query('C15').value('.', 'nvarchar(50)')as C15,
X.OBI.query('C16').value('.', 'nvarchar(50)')as C16,
X.OBI.query('C17').value('.', 'nvarchar(50)')as C17,
X.OBI.query('C18').value('.', 'nvarchar(50)')as C18,
X.OBI.query('C19').value('.', 'nvarchar(50)')as C19,
X.OBI.query('C20').value('.', 'nvarchar(50)')as C20,
X.OBI.query('C21').value('.', 'nvarchar(50)')as C21,
X.OBI.query('C22').value('.', 'nvarchar(50)')as C22,
X.OBI.query('C23').value('.', 'nvarchar(50)')as C23,
X.OBI.query('C24').value('.', 'nvarchar(50)')as C24
FROM (SELECT CAST(x AS XML)FROM OPENROWSET(BULK 'C:\Users\vvailaya\Desktop\Automation Scripts\Bookings\Business_Bookings.xml',SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY x.nodes('RS/R') AS X(OBI);

Thanks & Regards,
Venkatesh

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-08 : 20:09:38

How much data is there in each of the 600 rows? i.e., how many rows are inserted into the final destination table after the data is shredded?

Break it into two parts to see where the bottleneck is. First insert the data from the file into a table with a single XML column (without shredding it), for example:

INSERT INTO #StagingTable
SELECT CAST(x AS XML)FROM OPENROWSET(BULK 'C:\XML\Units_XML_Test.xml',SINGLE_BLOB) AS T(x)

This will tell you only limited info. I suspect this will go fast. If that indeed is the case, see whether there are XML indexes on the column. If there are it MAY (or MAY NOT) speed it up if you shred it into a staging table that does not have the indexes and then insert into your final destination table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources