January 7, 2012 at 10:26 am
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.
January 7, 2012 at 3:39 pm
The xml structure has a declared namespace (xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" ) that needs to be referenced in the query:
;
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);
January 7, 2012 at 10:38 pm
Thanks Lutz! It worked! 🙂 You Rock!
Thanks & Regards,
Venkatesh Vailaya
January 8, 2012 at 4:38 am
Hi Lutz,
I tried to execute the similar code with additional columns (25 total columns) and found that the performance is very poor. It took me more than 5hrs to fetch 600 rows of data from XML file.
How can I improve the performance of this query to fetch rows faster?
Here’s the code that I am using now and XML file is similar to first one with additional columns.
;
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);
Also attached is the SQL execution plan.
Can you please help me out to improve the performance of this sql. I am trying to insert data from the XML file to SQL table with each xml file having 50 - 60K records. Hence if execution takes more time like this, its not favaourable for automate the process with XML data file reads.
Appreciate your help!
I am newbie to this forum, I am not sure if the information that I have provided is good enough to look at the performance issue. Please let me know if any addition info is required to help me out.
Thanks,
Venkatesh
January 8, 2012 at 5:02 am
My first recommendation is to load the data from the file into a table before shredding it and then replace the OPENROWSET part in your query with that table reference. It might also help to index the xml column.
January 8, 2012 at 6:43 am
Small tweak to Lutz' original code:
WITH XMLNAMESPACES (DEFAULT 'urn:schemas-microsoft-com:xml-analysis:rowset')
INSERT Unit_Test
([End User Name], [Country Code], Units)
SELECT
X.OBI.value('(C0/text())[1]', 'NVARCHAR(100)'),
X.OBI.value('(C1/text())[1]', 'NVARCHAR(2)'),
X.OBI.value('(C2/text())[1]', 'INTEGER')
FROM
(
SELECT
CONVERT(XML, x)
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);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 8, 2012 at 6:57 am
Combine the tweak Paul provided and the preload in a temp table and you should see a significant performance boost.
Based on that you should try whether an xml index will further improve performance or not (it depends on the xml structure if the additional time to create the index will be compensated by the improved query time. Only tests will tell 😉 ).
@paul-2: thank you for the reminder! I tend to forget that ( < node > /text())[1] usually makes quite a difference...
January 8, 2012 at 7:13 am
LutzM (1/8/2012)
@Paul: thank you for the reminder! I tend to forget that ( < node > /text())[1] usually makes quite a difference...
You're welcome (but removing the extra .query calls is the main thing I expect to improve performance here).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 8, 2012 at 7:28 am
SQL Kiwi (1/8/2012)
LutzM (1/8/2012)
@Paul: thank you for the reminder! I tend to forget that ( < node > /text())[1] usually makes quite a difference...You're welcome (but removing the extra .query calls is the main thing I expect to improve performance here).
OUCH!! I didn't even notice the .query! :blush:
I just copied the original code and added the namespace stuff. Silly me....
January 9, 2012 at 11:09 pm
Thank You Lutz & Paul! Peformance is now improved to great extent after I incorprated your suggestions.
Here's Sample code
--Using (< node > /text())[1]
WITH XMLNAMESPACES (DEFAULT 'urn:schemas-microsoft-com:xml-analysis:rowset')
INSERT #Unit_Test ([End User Name], [Country Code], Units)
SELECT
X.OBI.value('(C0/text())[1]', 'NVARCHAR(100)'),
X.OBI.value('(C1/text())[1]', 'NVARCHAR(2)'),
X.OBI.value('(C2/text())[1]', 'INTEGER')
FROM (SELECT CONVERT(XML, x) 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);
---I tried the above query for 25 column results set, with this method the performance was better than earlier, i.e. 260 rows in 48mins.
-----------------------------------------------------------------------------------
---Using (< node > /text())[1] & preload the XML file in temp table
CREATE Table #XML
( XMLData XML )
INSERT INTO #XML (XMLData)
SELECT CONVERT(XML, x) FROM OPENROWSET(BULK 'C:\XML\Units_XML_Test.xml', SINGLE_BLOB) AS T(x)
WITH XMLNAMESPACES (DEFAULT 'urn:schemas-microsoft-com:xml-analysis:rowset')
INSERT #Unit_Test ([End User Name], [Country Code], Units)
SELECT
X.OBI.value('(C0/text())[1]', 'NVARCHAR(100)'),
X.OBI.value('(C1/text())[1]', 'NVARCHAR(2)'),
X.OBI.value('(C2/text())[1]', 'INTEGER')
FROM (Select XMLData from #XML) AS T(x)
CROSS APPLY x.nodes('RS/R') AS X(OBI);
--Again with 25 column results set, performance with this method was very good. Entire XML file data was loaded in 4seconds (4366 rows).
Thanks guys for your help!:-)
Best Regards,
Venkatesh
January 10, 2012 at 9:09 am
Well, from 600 rows in 5hrs to 4366 rows in 4 sec is quite an improvement!!
Thank you for posting the result!
And again, I'm sorry for not detecting the .query issue in the first place... But, fortunately, there's a large number of good eyes available cleaning up the mess after me 😀 (thanx again, Paul)
August 8, 2018 at 3:18 pm
Hi , I am facing the same issue
In my XML I have the following data:
<Transaction xmlns="http://www.abc.com/xdata/100" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
and in SQL i am declaring the namespace as:
;WITH XMLNAMESPACES (DEFAULT'http://www.abc.com/xdata/100')
and it is not working for me.
Please add If i am missing anything.
Thanks!!
August 8, 2018 at 9:58 pm
dishaarora13 - Wednesday, August 8, 2018 3:18 PMHi , I am facing the same issue
In my XML I have the following data:
<Transaction xmlns="http://www.abc.com/xdata/100" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
and in SQL i am declaring the namespace as:
;WITH XMLNAMESPACES (DEFAULT'http://www.abc.com/xdata/100')
and it is not working for me.Please add If i am missing anything.
Thanks!!
Suggest you start a new thread on the subject as the original one is 6 years old😉
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply