Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Iterating xml value Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 5:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:02 AM
Points: 29, Visits: 107
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.

Post #1523620
Posted Tuesday, December 17, 2013 1:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:05 PM
Points: 7,084, Visits: 14,678
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?
Post #1523870
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse