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.