Query from XML

• juho

  SSC Journeyman

  Points: 80

  More actions

  August 28, 2008 at 11:49 pm

  #197712

  Hi

  I haven't really used SQL XML before and now that I need to use it I find myself in trouble.

  I have a table that looks like this:

  create table #testA (

  xml xml

  )

  insert #testA

  select '<Tree a="1"><Leaf b="1" /><Leaf b="2" /></Tree>'

  union

  select '<Tree a="2"><Leaf b="3" /></Tree>'

  union

  select '<Tree a="3"><Leaf b="4" /><Leaf b="5" /></Tree>'

  And I need to create a query that would give this as result:

  A|B

  ---------

  1|1

  1|2

  2|3

  3|4

  3|5

  I managed to create a really ugly looking query to do the job:

  create table #Result (

  A varchar(max),

  B varchar(max)

  )

  declare @x xml

  declare @x2 xml

  declare @tree varchar(max)

  declare c cursor for select [xml] from #testA

  open c

  fetch next from c into @x

  while @@fetch_status=0 begin

  select @tree=@x.value('/Tree[1]/@a[1]','varchar(max)'), @x2=@x.query('/Tree/Leaf')

  insert #Result

  select

  @tree,

  T.c.value('@b','varchar(max)')

  from @x2.nodes('/Leaf') T(c)

  fetch next from c into @x

  end

  close c

  deallocate c

  select * from #Result

  drop table #Result

  I'm certain there is an better, easier and neater way to do it.

  I Just can't figure it out.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  August 29, 2008 at 1:29 am

  #864856

  select r.value('../@a','int') as A,

  r.value('@b','int') as B

  from #testA

  cross apply xml.nodes('/Tree/Leaf') as x(r)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• juho

  SSC Journeyman

  Points: 80

  More actions

  August 29, 2008 at 1:43 am

  #864861

  Thanks!

  It works. I knew I was doing it wrong.