April 19, 2012 at 2:55 am
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... 🙂
April 19, 2012 at 4:33 am
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!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply