December 17, 2013 at 5:28 am
Hi All,
I have a xml field as
<Emp>
<Details>
<D N="Id" V="1235" />
<D N="Des" V="SW" />
<D N="name" V="AAA" />
<D N="name" V="BBB" />
<D N="name" V="CCC" />
<D N="name" V="DDD" />
</Details>
</Emp>
.. I want the Output as AAA,BBB,CCC,DDD when input parameter is name .
Can some one please help me to get the desired output.
THanks in advance.
December 17, 2013 at 1:14 pm
Try something like:
declare @x varchar(2000)
set @x='<Emp>
<Details>
<D N="Id" V="1235" />
<D N="Des" V="SW" />
<D N="name" V="AAA" />
<D N="name" V="BBB" />
<D N="name" V="CCC" />
<D N="name" V="DDD" />
</Details>
</Emp>'
;with XMLCTE as (select cast(@x as XML) xmlfield)
select xmlfield,r.value('(.)/@V','varchar(350)')
from xmlcte
cross apply xmlfield.nodes('/Emp/Details/D[@N="name"]') a(r)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply