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)