"Liyasker Samraj" <LiyaskerSam***@discussions.microsoft.com> wrote in
message news:1466BE5E-CE93-4C39-AEC8-5EC3D9084035@microsoft.com...
> -- 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