Great post.

I have a question about parent nodes.

This is my Xml.

<?xml version="1.0" encoding="utf-16" standalone="yes"?>

<PropertyExport Version="1.2">

<Property Id="85" DescId="2088">

<PhoneList>

<Phone PhoneTypeId="48" PhoneTypeString="Booking">

<AreaCode />

<CountryCode>45</CountryCode>

<Number>70121700</Number>

</Phone>

<Phone PhoneTypeId="51" PhoneTypeString="FaxBooking">

<AreaCode />

<CountryCode>45</CountryCode>

<Number>33239686</Number>

</Phone>

<Phone PhoneTypeId="52" PhoneTypeString="Reception">

<AreaCode />

<CountryCode>45</CountryCode>

<Number>33314801</Number>

</Phone>

</PhoneList>

</Property>

</PropertyExport>

I can find all the <Number> and <CountryCode> nodes, like this:

SELECT

T.c.value('(CountryCode/text())[1]', 'varchar(256)') as CountryCode,

T.c.value('(Number/text())[1]', 'varchar(256)') as PhoneNumber

FROM @x.nodes('//Property/PhoneList/Phone') T(c)

But i also want to get the id attribute from the <Property> node in the same select statement. How is that done?

SELECT

T.c.value('parentnode/parentnode/parentnode/@Id', 'int') as PropertyId,

T.c.value('(CountryCode/text())[1]', 'varchar(256)') as CountryCode,

T.c.value('(Number/text())[1]', 'varchar(256)') as PhoneNumber

FROM @x.nodes('//Property/PhoneList/Phone') T(c)