tsql - Nesting of FOR XML Expressions -


i tried xml query similar what's shown here under subtitle 'nesting of xml expressions'. problem have have 3 xml element levels. results should this.

<steptree name="alfki">   <step name="foo">     <result id="123" />     <result id="456" />   </step>   <step name="bar">     <result id="789" />     <result id="987" />   </step> </steptree > 

i tried type of query.

select 1 tag,        null parent,        steptrees.name [steptree!1!name],        null [step!2!name],        null [result!3!id] steptrees steptrees.name = 'alfki'  union select 2,        1,        steptrees.name,        steps.name,        null steps join steptrees on steps.steptreeid = steptrees.id steptrees.name = 'alfki'  union select distinct 3,        2,        steptrees.name,        steps.name,        results.id steptrees join steps on steps.steptreeid = steptrees.id join results on steps.stepid = results.stepid xml explicit 

the resulting xml follows.

<steptree name="alfki">   <step name="foo" />   <step name="bar">     <result id="123" />     <result id="456" />     <result id="789" />     <result id="987" />   </step> </steptree > 

any ideas?

use query:

select 1 tag,    null parent,    steptrees.name [steptree!1!name],    null [step!2!name],    null [result!3!id] steptrees steptrees.name = 'alfki'  union select 2,        1,        null,        steps.name,        null steps join steptrees on steps.steptreeid = steptrees.id steptrees.name = 'alfki'  union select distinct 3,        2,        null,        steps.name,        results.id steptrees join steps on steps.steptreeid = steptrees.id join results on steps.stepid = results.stepid order [step!2!name],[result!3!id] xml explicit 

Comments

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -