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
Post a Comment