How to use Distinct in XML Column

We have used XML Column in our Table.

I want to select a distict values from the xml colunm.

is there any way to use distinct?

Regards

Vasanth Thangasamy

Do you want to get XML out, or a single row rowset or multiple rows?

Can you paste your xml structure?

|||

This is my XML Structure. I have stored this XML in a colunm. Here i want to fetch the disticnt of XML/Directors/Dir/Name........

<xml>

<MovieTitle>Vettaiyadu Vilaiyadu</MovieTitle>

<ImgName>KamalJyo.jpg</ImgName>

<Directors>

<Dir>

<Name>DirYou</Name>

<Status>N/A</Status>

</Dir>

<Dir>

<Name>DireMe</Name>

<Status>Pending</Status>

</Dir>

<Dir>

<Name>Gowtham</Name>

<Status>Killed</Status>

</Dir>

<Dir>

<Name>Manirat</Name>

<Status>N/A</Status>

</Dir>

</Directors>

<CastMem>

<CM>

<CM_Name>Kamal</CM_Name>

<Status>N/A</Status>

</CM>

<CM>

<CM_Name>Jyothika</CM_Name>

<Status>Aprvd</Status>

</CM>

<CM>

<CM_Name>PrakshRaj</CM_Name>

<Status>Pending</Status>

</CM>

<CM>

<CM_Name>Asai</CM_Name>

<Status>Killed</Status>

</CM>

</CastMem>

<ImgNum>2128</ImgNum>

<Tags>

<Tag>

<value>FIR</value>

</Tag>

<Tag>

<value>SEC</value>

</Tag>

<Tag>

<value>THI</value>

</Tag>

<Tag>

<value>FOR</value>

</Tag>

</Tags>

<ImgSize>

<Ht>21.2</Ht>

<Wt>22.2</Wt>

<Si>222.2</Si>

</ImgSize>

<WebAccess>True</WebAccess>

</xml>

|||

You can use the nodes method to extract the values you are interesting in and then use DISTINCT in the projection.

For example:

declare @.x xml
set @.x = N'<you xml here/>'

select distinct ref.a.value('text()[1]', 'nvarchar(25)') as name
from @.x.nodes('/xml/Directors/Dir/Name') ref(a)

If, on the other hand, you intend to use the distinct values inside of XQuery, you can use the function fn:distinct-values:

select @.x.query('fn:distinct-values(/xml/Directors/Dir/Name)')

Regards,

Galex