Parse XML Parent Node Value in Xquery?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Liyasker Samraj <LiyaskerSamraj@xxxxxxxxxxxxxxxxxxxxxxxxx>
Date: Thu, 10 May 2007 00:04:03 -0700

-- Copy paste it will work --
DECLARE @HouseTypeCollectionXML XML
SET @HouseTypeCollectionXML =
'
<ArrayOfCType>
<CType>
<Key>1</Key>
<Description>Site Built type 1</Description>
<ResTypes>
<RType>
<Key>1</Key>
</RType>
<RType>
<Key>2</Key>
</RType>
<RType>
<Key>3</Key>
</RType>
<RType>
<Key>4</Key>
</RType>
<RType>
<Key>5</Key>
</RType>
</ResTypes>
</CType>
<CType>
<Key>2</Key>
<Description>Site Built Type 2</Description>
<ResTypes>
<RType>
<Key>6</Key>
</RType>
</ResTypes>
</CType>
</ArrayOfCType>

'

SELECT HouseType.query('Key').value('.','INT') AS CTypeID

,HouseType.query('ResTypes/RType/Key').value('.','INT') AS
Rtype

FROM @HouseTypeCollectionXML.nodes('/ArrayOfCType/CType')
HouseTypeCollection(HouseType)

WHERE HouseType.exist('ResTypes/RType/Key') = 1
/*
Resullt :
CTypeID Rtype
1 12345
2 6

But i want that the result should be :

1 1
1 2
1 3
1 4
1 5
2 6
*/

thanks
Sam K
.

Follow-Ups:
- Re: Parse XML Parent Node Value in Xquery?
  - From: Han

Prev by Date: Re: Using sp_executesql to dynamically query xml
Next by Date: Newbie - generate XML tree from parent-child hierarchy
Previous by thread: Re: Using sp_executesql to dynamically query xml
Next by thread: Re: Parse XML Parent Node Value in Xquery?
Index(es):
- Date
- Thread