FOR XML EXPLICIT formatting issue

• BeerBeerBeer

  Hall of Fame

  Points: 3961

  More actions

  March 14, 2006 at 4:00 am

  #67310

  I am trying to use FOR XML EXPLICIT to group records that are related.

  I want to achieve something like

  I keep getting

  The query looks something like

  SELECT

  1 AS Tag

  , NULL AS Parent

  , header AS 'Header!1!Id'

  ::

  ::

  , NULL AS 'Child1!2!Child1Id'

  , NULL AS 'Child1!2!Name'

  , NULL AS 'Child2!3!Child1Id'

  , NULL AS 'Child2!3!Name'

  FROM

  blah, blah, blah

  UNION ALL

  SELECT

  1 AS Tag

  , NULL AS Parent

  , NULL AS 'Header!1!Id'

  ::

  ::

  , ChildId AS 'Child1!2!Child1Id'

  , Name AS 'Child1!2!Name'

  , NULL AS 'Child2!3!Child1Id'

  , NULL AS 'Child2!3!Name'

  FROM

  blah, blah, blah

  UNION ALL

  SELECT

  1 AS Tag

  , NULL AS Parent

  , NULL AS 'Header!1!Id'

  ::

  ::

  , NULL AS 'Child1!2!Child1Id'

  , NULL AS 'Child1!2!Name'

  , ChildId AS 'Child2!3!Child1Id'

  , Name AS 'Child2!3!Name'

  FROM

  blah, blah, blah

  Any help is gratefully appreciated.

  Let me know if I am on the wrong path also, as I wouldn't be surprised

  Thanks

  Steve

  ---

  Regards,

  Steve

  Life without beer is no life at all

  All beer is good, some beers are just better than others
  

• Eddie Wuerch

  SSChampion

  Points: 12532

  More actions

  March 14, 2006 at 8:25 am

  #626538

  You need to include an ORDER BY:

  ORDER BY [Header!1!Id], [Child1!2!Child1Id], [Child2!3!Child2ID], [blah!blah!blahId]

  -Eddie

  Eddie Wuerch
  MCM: SQL

• BeerBeerBeer

  Hall of Fame

  Points: 3961

  More actions

  March 14, 2006 at 1:09 pm

  #626632

  Excellent, so close yet so far.

  Also, in my actual code I had not propogated the Header Id into the other parts of the union, it din't actually know what the relationships were.

  And now BizTalk seems to like it.

  Thanks

  ---

  Regards,

  Steve

  Life without beer is no life at all

  All beer is good, some beers are just better than others