Hi ,
I want to return the xml datatype values in the output of my query.
I am querrying on one table which has xml datatype by selelcting the column in select clause and would like to get the values in output.
How do i get output with out/with including the column name in group by clause?
Can some one help me here.
--Smita.
You have a XML column and you want to break the XML as columns ? is this what you want ?|||Rick,
I have a coulmn with xml datatype and want to get entire column value in one output.
-Smitha.
|||Something like this ?
declare @.x xml
set @.x='<Root>
<Node>
<Node name="S.No">1</Node>
<Node name="S.Level">1</Node>
<Node name="S.FName">Data1</Node>
<Node name="S.LName">Data2</Node>
<Node name="Sl #" />
</Node>
<Node>
<Node name="S.No">2</Node>
<Node name="S.Level">2</Node>
<Node name="S.FName">Data22</Node>
<Node name="S.LName">Data33</Node>
<Node name="Sl #" />
</Node>
</Root>'
select cast(r.query('for $data in ./Node return data($data) ') as varchar(max)) as [Column]
from @.x.nodes('/Root/Node') as X(r)
