Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 LOADING COMPLEX XML FILE Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, July 3, 2013 2:16 AM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, July 12, 2016 2:02 PM Points: 3,000, Visits: 1,775
 It doesn't get dumped anywhere, it is just a select query.To insert the transformed xml into a table, just add an insert statement before the select i.e.;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)INSERT INTO SomeTable (SomeXmlColumn)SELECT @xml.query(' { for $x in //d1p1:Worker return {$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()} {data($x/d1p1:Worker_Reference/@d1p1:Descriptor)} }') Post #1469900  Posted Wednesday, July 3, 2013 11:03 AM  SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 6, 2016 12:33 PM Points: 113, Visits: 245  Hello. Thanks for your help. Some section of the codes didnt work but I have been reading your blog and some other articles on xquery. I will tell you what works and what didnt as well as my plan, moving forwardNumber 1DECLARE @xml XMLSELECT @xml = (SELECT * FROM OPENROWSET(BULK 'D:\chamila\XMLTest\text1.xsd', SINGLE_BLOB) x)This works - It gives: "Command(s) completed successfully"Number 2DECLARE @xml XML;WITH xmlnamespaces ('urn:com.workday/bsvc' as d1p1)SELECT @xml.query(' { for$x in //d1p1:Worker return {$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()} {data($x/d1p1:Worker_Reference/@d1p1:Descriptor)} }')This gives: (No column name)NULLNumber 3WITH xCTE AS(SELECT @xml.query(' { for $x in //d1p:Worker return {$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()} {data($x/d1p1:Worker_Reference/@d1p1:Descriptor)} } ') AS DocXml)SELECT t.c.value('(Value/text())[1]', 'varchar(10)') AS 'EmployeeID' , t.c.value('(PrevValue/text())[1]', 'varchar(10)') AS 'FullName' , t.c.value('(NextValue/text())[1]', 'varchar(10)') AS 'NextSomeElementValue'FROM xCTECROSS APPLY DocXml.nodes('/Data/SomeElement') AS T(c);This gives:Msg 257, Level 16, State 3, Line 2Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this queryThe above is understandable because the table that I am trying to insert it into has EmployeeID declared as a varchar column.I thought the above is pulling two columns but when I edited the above to include 2 columns as in INSERT INTO UserReference2 (EmployeeID, FullName), I got the message belowMsg 120, Level 15, State 1, Line 3The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. Why is this? I thought that there are 2 elements - EmployeeID and FullName.I have rewritten the query using the CTE that I saw on your blog. This is very close to what I want as I want to store value not xml. So, I have rewritten it as below:DECLARE @xml XML;WITH xmlnamespaces ('urn:com.workday/bsvc' as d1p1);WITH xCTE AS(SELECT @XML.query(' { for$x in //d1p:Worker return {$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()} {data($x/d1p1:Worker_Reference/@d1p1:Descriptor)} } ') AS DocXml)SELECT t.c.value('(Value/text())[1]', 'varchar(10)') AS 'EmployeeID' , t.c.value('(PrevValue/text())[1]', 'varchar(10)') AS 'FullName'INTO UserReference2 FROM xCTECROSS APPLY DocXml.nodes('/Data/SomeElement') AS T(c);However, it is given me syntax error and the error is pointing to between the second and third line. What can I do to make this work? Many thanks in advance.
Post #1470168
 Posted Wednesday, July 3, 2013 12:17 PM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, July 12, 2016 2:02 PM Points: 3,000, Visits: 1,775
 Ok, we don't need to transform the xml first via xquery if you only want to shred the xml to a flat table. from your original post it appeared that you wanted to transform the xml that you had to a new format.. this piece of code will load your file and then shred the two values out to a tableDECLARE @xml XMLSELECT @xml = (SELECT * FROM OPENROWSET(BULK 'd:\chamila\XMLTest\text1.xsd', SINGLE_BLOB) x);with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)SELECT t.c.value('(d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text())[1]', 'int') as WorkerId , t.c.value('(d1p1:Worker_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') as FullNameFROM @xml.nodes('//d1p1:Worker') t(c)however I must ask that the filename you are loading has an xsd extension. normally this is used for xml schemas and not xml data. is this file an xml schema or does the file actually contain xml data like that you have posted previously.
Post #1470193
 Posted Thursday, July 4, 2013 3:11 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 6, 2016 12:33 PM Points: 113, Visits: 245
 Thank you so much. I really don't know what to say but you have basically did this for me. I have ammended the query as a SELECT INTO to dump the data into a table but the EmployeeID was not been picked up. The column contain null throughout. Only the full name was picked. How can I ammend it to pick the corresponding EmployeeID for each FullName?Thanks.
Post #1470352
 Posted Thursday, July 4, 2013 3:30 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 6, 2016 12:33 PM Points: 113, Visits: 245
 I have ammended it as below and it is now picking EmployeeID as well. Thanks and God bless. I will give you a shout when I get to other section of the xml. Those are actually more complex than this because up to 20 fields are involved. Once again, thanks.DECLARE @xml XMLSELECT @xml = (SELECT * FROM OPENROWSET(BULK 'd:\chamila\XMLTest\text1.xml', SINGLE_BLOB) x);with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)SELECT t.c.value('(d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Employee_ID"]/text())[1]', 'int') as WorkerID , t.c.value('(d1p1:Worker_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') as FullName--INTO UserReference4FROM @xml.nodes('//d1p1:Worker') t(c)
Post #1470363
 Posted Thursday, July 4, 2013 3:37 AM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, July 12, 2016 2:02 PM Points: 3,000, Visits: 1,775
 Hi.. I'm glad I could help :)
Post #1470366
 Posted Thursday, July 4, 2013 11:35 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 6, 2016 12:33 PM Points: 113, Visits: 245
 Hi Arthur,Thanks for introducing me to XQuery. Unfortunately, my knowledged is not enough to handle the data that I am pulling. The next one that I am pulling is nested and has the same information repeated in the same section for a single worker.The copied code for a single worker is pasted below:- - 14009 - - - - - c4f78be1a8f14da0ab49ce1162348a5e BR BRI - - ba15d1e6bc1f4263982db59ac0e7ad21 Mr_Brazil Ola Ola - - - c4f78be1a8f14da0ab49ce1162348a5e BR BRI - - ba15d1e6bc1f4263982db59ac0e7ad21 Mr_Brazil Ola Handa - - - c4f78be1a8f14da0ab49ce1162348a5e IN IND 2013-02-19T10:20:48.415+00:00 Floor 8 Shad Tower T Block s16 Inaolaji Shopping Complex Manaus 400 051 - - - 1f27f250dfaa4724ab1e1617174281e4 WORK - - c4f78be1a8f14da0ab49ce1162348a5e BR BRI 2013-02-24T19:46:36.41+00:00 Flat 9886, La Guesepe Beverly Hill, Off Beverly Road East West 144498 - - - 836cf00ef5974ac08b786079866c946f HOME - 9385649a0c4b4ddea7fb0d823c2f1bcd MAILING - ola@sqlservercentral.com - - - 1f27f250dfaa4724ab1e1617174281e4 WORK false Part of the nesting as I understand is 91000 ....I wrote the code below but it only return null valuesDECLARE @xml XMLSELECT @xml = (SELECT * FROM OPENROWSET(BULK 'd:\chamila\XMLTest\Personal1.xml', SINGLE_BLOB) x);with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)SELECT t.c.value('(d1p1:Worker_ID)[1]', 'int') as EmployeeID , t.c.value('(d1p1:Name_Detail_Data/@d1p1:Reporting_Name)[1]', 'varchar(100)') as FullName , t.c.value('(d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') as CountryISOCodeINTO UserReference5FROM @xml.nodes('//d1p1:Worker') t(c)I thought it might be because I didnt wrapped (//Worker_Data/Worker_ID) them as they appear down the nesting.I read your blogs and I understand how to do this with normal xml but not with the complex ones that I am dealing with. Could you please, advise me on how to do this?Thanks.
Post #1470490
 Posted Thursday, July 4, 2013 1:56 PM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, July 12, 2016 2:02 PM Points: 3,000, Visits: 1,775
 Hi. when you are specifying xpaths (that is the xml path to the node you want), you need to specify either the full path to the xml node that you want, or use the // to search anywhere from that point or lower in the xml. Personally i prefer to always be explicit and specify the full path to the node that i want instead of using //. this is because I want to be sure i am getting the node that i want and not run the risk of hitting an identically named node from elsewhere in the xml document. In the the query that we working on, the node is the starting point as what is specified in the nodes() function. so in your query you need to specify an xpath from that starting point in the xml and you need to specify each level in the xml to get to the node that you want.so for example:;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)SELECT t.c.value('(d1p1:Worker_Data/d1p1:Worker_ID)[1]', 'int') as EmployeeID , t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/@d1p1:Reporting_Name)[1]', 'varchar(100)') as FullName, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') as CountryISOCodeFROM @xml.nodes('//d1p1:Worker') t(c)
Post #1470503
 Posted Monday, July 8, 2013 5:36 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 6, 2016 12:33 PM Points: 113, Visits: 245