T-SQL on XML (using XQuery)

Question

I have the below XML

<myroot>
<scene>
<sceneId>983247</sceneId>
<item>
<coordinates>
<coordinate>0</coordinate>
<coordinate>1</coordinate>
<coordinate>2</coordinate>
<coordinate>3</coordinate>
</coordinates>
<Values>
<Value>34</Value>
<Value>541</Value>
<Value>255</Value>
<Value>332</Value>
</Values>
</item>
</scene>
</myroot>

How can I get using TSQL the following result:

Thanks,

M

user357812 · Accepted Answer · 2010-09-13 16:03:23Z

1

This XPath 2.0 expression:

/myroot/scene/item/
   string-join(for $pos in (0 to max(*/count(*)))
               return string-join(for $col in (1 to max(count(*)))
                                  return if ($pos=0)
                                         then concat('Col',$col)
                                         else *[$col]/*[$pos],
                                  ' '),
               '&#xA;')

Output:

answered Sep 13, 2010 at 16:03

user357812

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

2 Comments

koumides Over a year ago

Trying to run it like this: select @xml.query(' /myroot/scene/item/ string-join(for $pos in (0 to max(/count())) return string-join(for $col in (1 to max(count(*))) return if ($pos=0) then concat('Col',$col) else *[$col]/*[$pos], ' '), '
')') Doesn't seem to work

user357812 Over a year ago

@koumides: fn:count() must have one argument. The first fn:max invocation counts the maximum number of element into some "column". So it must be max(*/count(*)).

Peter Radocchia · Accepted Answer · 2010-09-14 16:10:08Z

Here's my XML noob approach.

If you only trust the element sequencing, and not the coordinate values themselves being a sequence:

select
  coordinate  = max(case when element = 'coordinate' then elemval end)
, value       = max(case when element = 'Value' then elemval end)
from (
  select 
    element   = row.value('local-name(.)','varchar(32)')
  , elemval   = row.value('.','int')
  , position  = row.value('for $s in . return count(../*[. << $s]) + 1', 'int')
  from @xml.nodes('/myroot/scene/item/*/*') a (row)
  ) a
group by position

Alternatively written as two .nodes() and a JOIN (you get the idea).

If do you trust the coordinate numbering to be a sequence starting at zero:

select 
  coordinate = row.value('for $s in . return count(../*[. << $s]) + 1', 'int')
             - 1
, value      = row.value('.','int')
from @xml.nodes('/myroot/scene/item/Values/*') a (row)

If you only trust the coordinate numbering to be a sequence, but from an arbitrary seed:

select 
  coordinate = row.value('for $s in . return count(../*[. << $s]) + 1', 'int')
             + row.value('(/myroot/scene/item/coordinates/coordinate)[1]','int')
             - 1
, value      = row.value('.','int')
from @xml.nodes('/myroot/scene/item/Values/*') a (row)

Paths can be abbreviated:

/myroot/scene/item/*/* -> //item/*/*
/myroot/scene/item/Values/* -> //Values/*
/myroot/scene/item/coordinates/coordinate -> //coordinate

But I don't know the wisdom of this either way.

//item/*/* can probably be made more specific, so that it only includes coordinate and Value edge nodes, but I don't know the syntax.

Collectives™ on Stack Overflow

T-SQL on XML (using XQuery)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related