parse xml with attributes and values

Question

I have a sql query to fetch values from an xml. Below is the structure of xml

<workflow>
  <meta name="jira.update.author.name">xyz</meta>
  <meta name="jira.description">Mobility Story Workflow</meta>
  <meta name="jira.updated.date">1284423336959</meta>
    .
    .
</workflow>

Query used is

SELECT name,descr
FROM OPENXML(@hDoc, 'workflow/meta')
WITH 
 (

name [varchar](255) '@name',
descr [varchar](255) '../meta'

)

I need to get a table with results

name                         |  descr
-----------------------------------------------------
jira.update.author.name            xyz 
jira.description                   Mobility Story Workflow
jira.updated.date                  1284423336959

The select query fetches only xyz in the descr column. Any workaround?

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Aug 21, 2013 at 19:19

roman · Accepted Answer · 2013-08-21 20:13:42Z

1

select
    t.c.value('@name', 'nvarchar(max)') as name,
    t.c.value('text()[1]', 'nvarchar(max)') as descr
from @data.nodes('workflow/meta') as t(c)

answered Aug 21, 2013 at 20:13

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

parse xml with attributes and values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related