Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LOADING COMPLEX XML FILE


LOADING COMPLEX XML FILE

Author
Message
sahoong
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 245
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>
<d1p1Tongueage>1</d1p1Tongueage>
<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>
<d1p1Tongueage_Results>999</d1p1Tongueage_Results>
<d1p1Tongueage>1</d1p1Tongueage>
</d1p1:Response_Results>
<d1p1:Response_Data>


<d1p1:Worker>
<d1p1:Worker_Reference d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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 d1p1BigGrinescriptor="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
Arthur Olcot
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 1777
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
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 245
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
Arthur Olcot
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 1777
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
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 245
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
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 245
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
Arthur Olcot
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 1777
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
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 245
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 d1p1BigGrinescriptor="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
Arthur Olcot
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 1777
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/@d1p1BigGrinescriptor)}</FullName>
</Worker>
}
</Workers>
')


sahoong
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 245
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search