Showing posts with label distinctregardsvasanth. Show all posts
Showing posts with label distinctregardsvasanth. Show all posts

Friday, March 9, 2012

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