LOADING COMPLEX XML FILE

• sahoong

  SSCrazy

  Points: 2545

  More actions

  July 1, 2013 at 7:08 am

  #73379

  The file below came from a web request I want to load it into SQL Server but it is too complex for my XML knowledge?

  I want it in this format

  <?xml version="1.0"?>

  <Worker>

  <EmployeeID>Tove</EmployeeID>

  <FullName>Jani</FullName>

  </Worker>

  The upper part of the file below is the request header information. How do I even get rid of all these lines before the 1st <d1p1:Worker>? Also, I do not want the WID column that it included. I do not know where it got that? Any help will be highly appreciated.

  Many thanks in advance.

  Regards,

  Sahoong

  <?xml version="1.0" encoding="utf-8"?>

  <Get_Workers_ResponseType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" d1p1:version="v10" xmlns:d1p1="urn:com.workday/bsvc">

  <d1p1:Response_Filter>

  <d1p1:As_Of_Effective_Date>2013-07-01</d1p1:As_Of_Effective_Date>

  <d1p1:As_Of_Entry_DateTime>2013-07-01T19:50:00.729+01:00</d1p1:As_Of_Entry_DateTime>

  <d1p1:Page>1</d1p1:Page>

  <d1p1:Count>999</d1p1:Count>

  </d1p1:Response_Filter>

  <d1p1:Response_Group>

  <d1p1:Include_Reference>true</d1p1:Include_Reference>

  </d1p1:Response_Group>

  <d1p1:Response_Results>

  <d1p1:Total_Results>6119</d1p1:Total_Results>

  <d1p1:Total_Pages>7</d1p1:Total_Pages>

  <d1p1:Page_Results>999</d1p1:Page_Results>

  <d1p1:Page>1</d1p1:Page>

  </d1p1:Response_Results>

  <d1p1:Response_Data>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">b093245ded60424bad4a210c5b9d2a8f</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62001</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">498e6f017d804a5c9f3b9088376584f7</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62002</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">e16bc4aa43884f10af47fe85cc72f6e0</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62003</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">419a3b45fd754f329efa374a1d12ba35</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62004</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">c7b574bfe26d45cc8ba5f01874c142af</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62005</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">ea8567f202f74b52ae4607efd3d195bf</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62006</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">f03ebe2bc92348d1ac29adf8a4595b37</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62007</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">4c1496a9db9e4de2a4562bfbf683182f</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62008</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">4f552b567da54d29a3d85c4eccd6bdca</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62009</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">9c14dc2e2ea747a1841c3eae46caac6a</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62010</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">f9757acb37314359a197092cc5f50ede</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62011</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">60447c9970ef47739d64537714455ddf</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62012</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">616579cc52444a958c4e28b8ae856d18</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62013</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">095b38a44b664b83a3c9b4cc8b055b8b</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62014</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">e7a3d6f65ef144cc90e5d62f0f27f3a2</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62015</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">647cdd62615c4436b01f63d321e307c6</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62016</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">1af1bfcf8d4045acb90e1909fcbff694</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62017</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">667da06fd10643449ac0080ba7341319</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62018</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">9430ba942aa540edad97345594bdfb8d</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62019</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">6832fa97d5b742a48b31fffeb16f0d0a</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62020</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">c005cff1302d49fca43d411aba8ba240</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62021</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">9034af88c1294192a4b1c1fa871d5883</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62022</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">1c19956bf2fa42b89af401f39634b16b</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62023</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="FirsName LastName">

  <d1p1:ID d1p1:type="WID">5200c692f79e419dbab5d13005c1a0b6</d1p1:ID>

  <d1p1:ID d1p1:type="Employee_ID">62024</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

• Arthur Olcot

  SSCertifiable

  Points: 6564

  More actions

  July 2, 2013 at 1:57 am

  #1628929

  You can use a bit of xquery to transform the data. Here is an example to do that. Assuming the data is loaded into an xml variable called @xml

  ;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)

  SELECT @xml.query('

  <Workers>

  {

  for $x in //d1p1:Worker

  return

  <Worker>

  <EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Employee_ID"]/text()}</EmployeeID>

  <FullName>?? unknown ??</FullName>

  </Worker>

  }

  </Workers>

  ')

  Your example didn't have any full names in there that i could see so i couldn't do that part of the query... Hope this helps though.

• sahoong

  SSCrazy

  Points: 2545

  More actions

  July 2, 2013 at 6:20 am

  #1629026

  Many thanks for your response. It is highly appreciated. You are right, there is no FullName column. I am trying to replace Worker_Reference with FullName. I have never run an Xquery before. I will look for more information about running it but are you are able to tell me how to run it?

  Once again, many thanks for your help.

  Best regards.

• Arthur Olcot

  SSCertifiable

  Points: 6564

  More actions

  July 2, 2013 at 6:37 am

  #1629034

  Hi, xquery is just a language that you can use against an xml data type be it a variable or column. You can run it as part of a normal tsql batch.

  Here is a good place to get started with:

  http://msdn.microsoft.com/en-us/library/ms189075.aspx

  but there are 1000's of blogs out there on the subject.. I've done a few posts myself:

  http://www.olcot.co.uk/Tags/Xml

• sahoong

  SSCrazy

  Points: 2545

  More actions

  July 2, 2013 at 6:41 am

  #1629038

  This is the way I want the xml file to be

  <?xml version="1.0"?>

  <Worker>

  <Worker_Reference> Employee Name </Worker_Reference>

  <Employee_ID> 62005 </Employee_ID>

  </Worker>

  I will later replace Worker_Reference with FullName or simply map it during importation into the database.

  Please, give me further hints about getting this done.

• sahoong

  SSCrazy

  Points: 2545

  More actions

  July 2, 2013 at 9:23 am

  #1629170

  Thanks ever so MUCH. I really appreciate your help.

  SELECT @xml.query('

  <Workers>

  {

  for $x in //d1p1:Worker

  return

  <Worker>

  <EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Employee_ID"]/text()}</EmployeeID>

  <FullName>?? unknown ??</FullName>

  </Worker>

  }

  </Workers>

  ')

  If I have my data in a file and I want to run the above code against it, how do I include the file location in the query in the normal SELECT format as in:

  SELECT * FROM table_name

  ?

• Arthur Olcot

  SSCertifiable

  Points: 6564

  More actions

  July 2, 2013 at 9:36 am

  #1629183

  You'll need to read the xml data in from the file to a variable before running the example that I posted earlier.

  Reading in the data is quite straight forward and i explained how to do it here on this thread:

  http://www.sqlservercentral.com/Forums/Topic1451237-21-1.aspx

• sahoong

  SSCrazy

  Points: 2545

  More actions

  July 2, 2013 at 10:18 am

  #1629202

  Thanks. The storing in variable works fine but now that I have a better understanding of the process, I am getting confused with the file response you wrote me and how to get the xml file that I need from the one that I have.

  <d1p1:Worker>

  <d1p1:Worker_Reference d1p1:Descriptor="Olga Akinyo">

  <d1p1:ID d1p1:type="WID">af432f03c87f435f9ecb77460202f438</d1p1:ID>

  <d1p1:ID d1p1:type="Contingent_Worker_ID">69816</d1p1:ID>

  </d1p1:Worker_Reference>

  </d1p1:Worker>

  I want to Extract Full Name which is Ola

  and

  EmployeeID which is 69816.

  I am having problem matching the first one you sent me to the original text above

  Could you please, write me a mapping using the above to get the xml file below:

  <?xml version="1.0"?>

  <Worker>

  <Worker_Reference>Employee Name</Worker_Reference>

  <Employee_ID>62005</Employee_ID>

  </Worker>

  The first one you sent me is copied below:

  <Workers>

  {

  for $x in //d1p1:Worker

  return

  <Worker>

  <EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Employee_ID"]/text()}</EmployeeID>

  <FullName>?? unknown ??</FullName>

  </Worker>

  }

  </Workers>

  ')

• Arthur Olcot

  SSCertifiable

  Points: 6564

  More actions

  July 2, 2013 at 11:31 am

  #1629228

  Hi. That snippit of xml that you have pasted seems to be a little different to your original xml that you posted earlier on.

  Try this updated version of the query based on your new example xml format:

  ;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)

  SELECT @xml.query('

  <Workers>

  {

  for $x in //d1p1:Worker

  return

  <Worker>

  <EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()}</EmployeeID>

  <FullName>{data($x/d1p1:Worker_Reference/@d1p1:Descriptor)}</FullName>

  </Worker>

  }

  </Workers>

  ')

• sahoong

  SSCrazy

  Points: 2545

  More actions

  July 2, 2013 at 4:36 pm

  #1629314

  Thanks so much for your help. The query completed successfully but where is the formatted xml get dump? i want to insert it into a sql server table. right now, i do not know where the new xml file data are.

• Arthur Olcot

  SSCertifiable

  Points: 6564

  More actions

  July 3, 2013 at 2:16 am

  #1629402

  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('

  <Workers>

  {

  for $x in //d1p1:Worker

  return

  <Worker>

  <EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()}</EmployeeID>

  <FullName>{data($x/d1p1:Worker_Reference/@d1p1:Descriptor)}</FullName>

  </Worker>

  }

  </Workers>

  ')

• sahoong

  SSCrazy

  Points: 2545

  More actions

  July 3, 2013 at 11:03 am

  #1629624

  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 forward

  Number 1

  DECLARE @xml XML

  SELECT @xml = (SELECT * FROM OPENROWSET(BULK 'D:\chamila\XMLTest\text1.xsd', SINGLE_BLOB) x)

  This works - It gives:

  "Command(s) completed successfully"

  Number 2

  DECLARE @xml XML;

  WITH xmlnamespaces ('urn:com.workday/bsvc' as d1p1)

  SELECT @xml.query('

  <Workers>

  {

  for $x in //d1p1:Worker

  return

  <Worker>

  <EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()}</EmployeeID>

  <FullName>{data($x/d1p1:Worker_Reference/@d1p1:Descriptor)}</FullName>

  </Worker>

  }

  </Workers>

  ')

  This gives:

  (No column name)

  NULL

  Number 3

  WITH xCTE AS

  (

  SELECT @xml.query('

  <Workers>

  {

  for $x in //d1p:Worker

  return

  <Worker>

  <EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()}</EmployeeID>

  <FullName>{data($x/d1p1:Worker_Reference/@d1p1:Descriptor)}</FullName>

  </Worker>

  }

  </Workers>

  ') 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 xCTE

  CROSS APPLY DocXml.nodes('/Data/SomeElement') AS T(c);

  This gives:

  Msg 257, Level 16, State 3, Line 2

  Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this query

  The 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 below

  Msg 120, Level 15, State 1, Line 3

  The 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('

  <Workers>

  {

  for $x in //d1p:Worker

  return

  <Worker>

  <EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()}</EmployeeID>

  <FullName>{data($x/d1p1:Worker_Reference/@d1p1:Descriptor)}</FullName>

  </Worker>

  }

  </Workers>

  ') 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 xCTE

  CROSS 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.

• Arthur Olcot

  SSCertifiable

  Points: 6564

  More actions

  July 3, 2013 at 12:17 pm

  #1629642

  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 table

  DECLARE @xml XML

  SELECT @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 FullName

  FROM @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.

• sahoong

  SSCrazy

  Points: 2545

  More actions

  July 4, 2013 at 3:11 am

  #1629768

  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.

  😀

• sahoong

  SSCrazy

  Points: 2545

  More actions

  July 4, 2013 at 3:30 am

  #1629775

  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 XML

  SELECT @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 UserReference4

  FROM @xml.nodes('//d1p1:Worker') t(c)