July 4, 2013 at 3:37 am
Hi.. I'm glad I could help 🙂
July 4, 2013 at 11:35 am
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:
- <d1p1:Worker>
- <d1p1:Worker_Data>
<d1p1:Worker_ID>14009</d1p1:Worker_ID>
- <d1p1:Personal_Data>
- <d1p1:Name_Data>
- <d1p1:Legal_Name_Data>
- <d1p1:Name_Detail_Data d1p1:Reporting_Name="Ola, Ola" d1p1:Formatted_Name="Ola Ola">
- <d1p1:Country_Reference d1p1:Descriptor="Brazil">
<d1p1:ID d1p1:type="WID">c4f78be1a8f14da0ab49ce1162348a5e</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-2_Code">BR</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-3_Code">BRI</d1p1:ID>
</d1p1:Country_Reference>
- <d1p1:Prefix_Data>
- <d1p1:Title_Reference d1p1:Descriptor="Mr.">
<d1p1:ID d1p1:type="WID">ba15d1e6bc1f4263982db59ac0e7ad21</d1p1:ID>
<d1p1:ID d1p1:type="Predefined_Name_Component_ID">Mr_Brazil</d1p1:ID>
</d1p1:Title_Reference>
</d1p1:Prefix_Data>
<d1p1:First_Name>Ola</d1p1:First_Name>
<d1p1:Last_Name>Ola</d1p1:Last_Name>
</d1p1:Name_Detail_Data>
</d1p1:Legal_Name_Data>
- <d1p1:Preferred_Name_Data>
- <d1p1:Name_Detail_Data d1p1:Reporting_Name="Ola, Ola" d1p1:Formatted_Name="Ola Ola">
- <d1p1:Country_Reference d1p1:Descriptor="Brazil">
<d1p1:ID d1p1:type="WID">c4f78be1a8f14da0ab49ce1162348a5e</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-2_Code">BR</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-3_Code">BRI</d1p1:ID>
</d1p1:Country_Reference>
- <d1p1:Prefix_Data>
- <d1p1:Title_Reference d1p1:Descriptor="Mr.">
<d1p1:ID d1p1:type="WID">ba15d1e6bc1f4263982db59ac0e7ad21</d1p1:ID>
<d1p1:ID d1p1:type="Predefined_Name_Component_ID">Mr_Brazil</d1p1:ID>
</d1p1:Title_Reference>
</d1p1:Prefix_Data>
<d1p1:First_Name>Ola</d1p1:First_Name>
<d1p1:Last_Name>Handa</d1p1:Last_Name>
</d1p1:Name_Detail_Data>
</d1p1:Preferred_Name_Data>
</d1p1:Name_Data>
- <d1p1:Contact_Data>
- <d1p1:Address_Data d1p1:Effective_Date="1900-01-01" d1p1:Defaulted_Business_Site_Address="true" d1p1:Formatted_Address="Level 8 Vibgyor Tower G Block C62 Bandra Kurla Complex Brasilia- 400 051 India">
- <d1p1:Country_Reference d1p1:Descriptor="India">
<d1p1:ID d1p1:type="WID">c4f78be1a8f14da0ab49ce1162348a5e</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-2_Code">IN</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-3_Code">IND</d1p1:ID>
</d1p1:Country_Reference>
<d1p1:Last_Modified>2013-02-19T10:20:48.415+00:00</d1p1:Last_Modified>
<d1p1:Address_Line_Data>Floor 8 Shad Tower</d1p1:Address_Line_Data>
<d1p1:Address_Line_Data>T Block s16 Inaolaji Shopping Complex</d1p1:Address_Line_Data>
<d1p1:Municipality>Manaus</d1p1:Municipality>
<d1p1:Postal_Code>400 051</d1p1:Postal_Code>
- <d1p1:Usage_Data d1p1:Public="true">
- <d1p1:Type_Data d1p1:Primary="true">
- <d1p1:Type_Reference d1p1:Descriptor="Work">
<d1p1:ID d1p1:type="WID">1f27f250dfaa4724ab1e1617174281e4</d1p1:ID>
<d1p1:ID d1p1:type="Communication_Usage_Type_ID">WORK</d1p1:ID>
</d1p1:Type_Reference>
</d1p1:Type_Data>
</d1p1:Usage_Data>
</d1p1:Address_Data>
- <d1p1:Address_Data d1p1:Effective_Date="2010-07-01" d1p1:Defaulted_Business_Site_Address="false" d1p1:Formatted_Address="Flat 9886, La Guesepe Beverly Hill, Off Beverly Road East West- 144498 Brazil">
- <d1p1:Country_Reference d1p1:Descriptor="Brazil">
<d1p1:ID d1p1:type="WID">c4f78be1a8f14da0ab49ce1162348a5e</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-2_Code">BR</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-3_Code">BRI</d1p1:ID>
</d1p1:Country_Reference>
<d1p1:Last_Modified>2013-02-24T19:46:36.41+00:00</d1p1:Last_Modified>
<d1p1:Address_Line_Data>Flat 9886, La Guesepe</d1p1:Address_Line_Data>
<d1p1:Address_Line_Data>Beverly Hill, Off Beverly Road</d1p1:Address_Line_Data>
<d1p1:Municipality>East West</d1p1:Municipality>
<d1p1:Postal_Code>144498</d1p1:Postal_Code>
- <d1p1:Usage_Data d1p1:Public="false">
- <d1p1:Type_Data d1p1:Primary="true">
- <d1p1:Type_Reference d1p1:Descriptor="Home">
<d1p1:ID d1p1:type="WID">836cf00ef5974ac08b786079866c946f</d1p1:ID>
<d1p1:ID d1p1:type="Communication_Usage_Type_ID">HOME</d1p1:ID>
</d1p1:Type_Reference>
</d1p1:Type_Data>
- <d1p1:Use_For_Reference d1p1:Descriptor="Mailing">
<d1p1:ID d1p1:type="WID">9385649a0c4b4ddea7fb0d823c2f1bcd</d1p1:ID>
<d1p1:ID d1p1:type="Communication_Usage_Behavior_ID">MAILING</d1p1:ID>
</d1p1:Use_For_Reference>
</d1p1:Usage_Data>
</d1p1:Address_Data>
- <d1p1:Email_Address_Data>
<d1p1:Email_Address>ola@sqlservercentral.com</d1p1:Email_Address>
- <d1p1:Usage_Data d1p1:Public="true">
- <d1p1:Type_Data d1p1:Primary="true">
- <d1p1:Type_Reference d1p1:Descriptor="Work">
<d1p1:ID d1p1:type="WID">1f27f250dfaa4724ab1e1617174281e4</d1p1:ID>
<d1p1:ID d1p1:type="Communication_Usage_Type_ID">WORK</d1p1:ID>
</d1p1:Type_Reference>
</d1p1:Type_Data>
</d1p1:Usage_Data>
</d1p1:Email_Address_Data>
</d1p1:Contact_Data>
<d1p1:Tobacco_Use>false</d1p1:Tobacco_Use>
</d1p1:Personal_Data>
</d1p1:Worker_Data>
</d1p1:Worker>
Part of the nesting as I understand is
<Worker>
<Worker_Data>
<Worker_ID>91000</Worker_ID>
<Personal_Data>
<Name_Data>
.
.
.
.
I wrote the code below but it only return null values
DECLARE @xml XML
SELECT @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 CountryISOCode
INTO UserReference5
FROM @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.
July 4, 2013 at 1:56 pm
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 <Worker> 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 CountryISOCode
FROM @xml.nodes('//d1p1:Worker') t(c)
July 8, 2013 at 5:36 am
Hi Arthur,
Just stopped by to say 'A BIG THANK YOU'. You broke this down to me and introduced me to XQuery. This is my query now and it is working perfectly:
DECLARE @xml XML
SELECT @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_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:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS CountryISOCode
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/@d1p1:Formatted_Name)[1]', 'varchar(100)') AS [Full Name]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:Prefix_Data/d1p1:Title_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS Title
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:First_Name)[1]', 'varchar(100)') AS [First Name]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:Last_Name)[1]', 'varchar(100)') AS [Last Name]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[1]', 'varchar(100)') AS [Address Line 1]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[2]', 'varchar(100)') AS [Address Line 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Municipality)[1]', 'varchar(100)') City
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Postal_Code)[1]', 'varchar(100)') [Postal Code]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS Country
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[2]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Municipality)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Postal_Code)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS [Work Address]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Last_Modified)[1]', 'varchar(100)') AS [Last Modified]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[1]', 'varchar(100)') AS [Address Line 3]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[2]', 'varchar(100)') AS [Address Line 4]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Municipality)[1]', 'varchar(100)') [City 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Postal_Code)[1]', 'varchar(100)') [Postal Code 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS [Country 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[2]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Municipality)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Postal_Code)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS [Home Address]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Last_Modified)[1]', 'varchar(100)') AS [Last Modified 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Email_Address_Data/d1p1:Email_Address)[1]', 'varchar(100)') AS [Email Address]
INTO Personal1
FROM @xml.nodes('//d1p1:Worker') t(c)
Hope it could help someone else as well.
Once again, THANKS so much.
July 8, 2013 at 2:19 pm
No problem.. thank you for the nice feedback.. 🙂
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply