Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

LOADING COMPLEX XML FILE Expand / Collapse
Author
Message
Posted Monday, July 01, 2013 7:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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>
Post #1469041
Posted Tuesday, July 02, 2013 1:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,424, Visits: 1,566
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.
Post #1469328
Posted Tuesday, July 02, 2013 6:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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.
Post #1469441
Posted Tuesday, July 02, 2013 6:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,424, Visits: 1,566
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
Post #1469449
Posted Tuesday, July 02, 2013 6:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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.
Post #1469454
Posted Tuesday, July 02, 2013 9:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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

?

Post #1469608
Posted Tuesday, July 02, 2013 9:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,424, Visits: 1,566
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

Post #1469623
Posted Tuesday, July 02, 2013 10:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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>
')
Post #1469649
Posted Tuesday, July 02, 2013 11:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,424, Visits: 1,566
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>
')

Post #1469688
Posted Tuesday, July 02, 2013 4:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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.
Post #1469790
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse