FOR XML EXPLICIT - newie question

• Teutales01

  Mr or Mrs. 500

  Points: 522

  More actions

  April 19, 2012 at 2:55 am

  #267731

  Hi,

  I'm new to XML EXPLICIT. I need a parent tag around the second SELECT. I added a simple example, which shows the target I want to select and my current approach.

  -- initiating demo data

  CREATE TABLE #campaigns (ID INT, xmlData XML)

  INSERT INTO #campaigns VALUES (1,'<data>some data</data>')

  CREATE TABLE #contacts (ID INT, campaignsID INT, xmlData XML)

  INSERT INTO #contacts VALUES (1,1,'<value>a contact</value>')

  INSERT INTO #contacts VALUES (2,1,'<value>another contact</value>')

  --SELECT * FROM #campaigns

  --SELECT * FROM #contacts

  -- TARGET

  -- this is the target I want to select

  SELECT CONVERT(XML,'<campaign><data>some data</data><contacts><contact><value>a contact</value></contact><contact><value>another contact</value></contact></contacts></campaign>')

  -- this is my approach

  SELECT1 AS Tag,

  NULL AS Parent,

  xmlData AS [campaign!1],

  NULL AS [contact!2]

  FROM#campaigns AS oc

  WHEREID = 1

  UNION ALL

  SELECT2 AS Tag,

  1 AS Parent,

  oc.xmlData,

  con.xmlData

  FROM#Contacts AS con

  JOIN#Campaigns AS oc

  ONoc.ID = con.campaignsID

  WHEREcampaignsID = 1

  FOR XML EXPLICIT

  -- cleaning

  DROP TABLE #campaigns

  DROP TABLE #contacts

  I think it's quite simple but...

• Teutales01

  Mr or Mrs. 500

  Points: 522

  More actions

  April 19, 2012 at 4:33 am

  #1475925

  OK, this is the solution...

  SELECT1 AS Tag,

  NULL AS Parent,

  xmlData AS [campaign!1],

  NULL AS [contacts!3],

  NULL AS [contact!2]

  FROM#campaigns AS oc

  WHEREID = 1

  UNION ALL

  SELECT3 AS Tag,

  1 AS Parent,

  NULL,

  NULL,

  NULL

  UNION ALL

  SELECT2 AS Tag,

  3 AS Parent,

  NULL,

  NULL,

  con.xmlData

  FROM#Contacts AS con

  WHEREcampaignsID = 1

  FOR XML EXPLICIT

  Thanx for reading!