SQL Server XML XQuery select where clause attribute -


i have table following content

id | guid                                 | xmldefinitionid  1 | 5a0bfc84-13ec-4497-93e0-655e57d4b482 | 1  2 | e28e786b-0856-40b6-8189-0fbd68aa3e45 | 1 

and in table following xml structure stored:

<actionactivity displayname="displayname 1" isskipped="false" id="5a0bfc84-13ec-4497-93e0-655e57d4b482">...</actionactivity> <p:sequence displayname="prerequisites">     <actionactivity displayname="inner displayname 1" isskipped="false" id="e28e786b-0856-40b6-8189-0fbd68aa3e45">...</actionactivity> </p:sequence> <actionactivity displayname="displayname 2" isskipped="false" id="dcc936dd-73c9-43cc-beb4-c636647d4851">...</actionactivity> 

the table containing xml have following structure:

id | xml 1  | (xml structure defined above here) 

based on guid want show displayname. @ moment have following query returns null @ moment. later want every guid first table show displayname.

select        workflow       ,cast(workflow xml).value('data(//actionactivity[@id="73c9-43cc-beb4-c636647d4851"])[1]', 'nvarchar(50)') displayname   serializeddata 

anyone ideas show displayname sql query?

assuming xml stored in xml typed column, can way -otherwise you'll need cast column xml- :

select     g.guid, x.display_name guidtable g      inner join       (         select              t.id 'xml_id'             , c.value('@id', 'varchar(max)') 'guid'             , c.value('@displayname', 'varchar(max)') 'display_name'         xmltable t             cross apply t.xml.nodes('//actionactivity') aa(c)      ) x on x.guid = g.guid , x.xml_id = g.xmldefinitionid 

basically, above query shred xml @ actionactivity node. , joins shredded data guidtable on guid , xmldefinitionid columns.

output :

enter image description here

sql fiddle


Comments

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

php - How can I echo out this array? -