FOR XML (Structured)
-
Hi,
I'm attemting to construct a XML document from data stored in SQL tables. Below is a sample structure:
DECLARE @SubscriptionTABLE (
IDINT
,NameVARCHAR(128)
,DeliveryMethodVARCHAR(32)
);
INSERT INTO @Subscription (ID, Name, DeliveryMethod) VALUES (1, 'Dummy Name', 'EMAIL');
DECLARE @ExtensionTABLE (
IDINT
,ID_SubscriptionINT
,[To]VARCHAR(128)
,PriorityVARCHAR(128)
,[Subject]VARCHAR(128)
,BodyVARCHAR(128)
);
INSERT INTO @Extension (ID, ID_Subscription, [To], Priority, [Subject], Body) VALUES (1, 1, 'to@domain.com', 'Normal', 'This is the subject of the emai.', 'This is the body of the email.')
DECLARE @ParametersTABLE (
IDINT
,ID_SubscriptionINT
,Param1VARCHAR(128)
,Param2VARCHAR(128)
,Param3VARCHAR(128)
);
INSERT INTO @Parameters (ID, ID_Subscription, Param1, Param2, Param3) VALUES (1,1, 'ABC', 'DEF', 'XYZ');
INSERT INTO @Parameters (ID, ID_Subscription, Param1, Param2, Param3) VALUES (2,1, 'CBA', 'FED', 'ZYX');
The objective is to get the following XML structure:
DeliveryMethod="EMAIL">
DeliveryMethod="EMAIL">Could someone please point me in a direction. I have tried various methods, but cannot seem to get it right.
-
You can use FOR XML EXPLICIT
N 56°04'39.16"
E 12°55'05.25" -
Use FOR XML PATH
SELECT Name AS "@SubscriptionName",
DeliveryMethod AS "@DeliveryMethod",
(SELECT
(SELECT e.[To] AS "@Value", 'To' AS "@Name"
FROM @Extension e
WHERE e.ID_Subscription=s.ID
FOR XML PATH('EXTENSION'),TYPE),
(SELECT e.Priority AS "@Value", 'Priority' AS "@Name"
FROM @Extension e
WHERE e.ID_Subscription=s.ID
FOR XML PATH('EXTENSION'),TYPE),
(SELECT e.[Subject] AS "@Value", 'Subject' AS "@Name"
FROM @Extension e
WHERE e.ID_Subscription=s.ID
FOR XML PATH('EXTENSION'),TYPE),
(SELECT e.Body AS "@Value", 'Body' AS "@Name"
FROM @Extension e
WHERE e.ID_Subscription=s.ID
FOR XML PATH('EXTENSION'),TYPE)
FOR XML PATH('EXTENSIONS'),TYPE),
(SELECT
(SELECT p.Param1 AS "@Value", 'Param1' AS "@Name"
FOR XML PATH('PARAMETER'),TYPE),
(SELECT p.Param2 AS "@Value", 'Param2' AS "@Name"
FOR XML PATH('PARAMETER'),TYPE),
(SELECT p.Param3 AS "@Value", 'Param3' AS "@Name"
FOR XML PATH('PARAMETER'),TYPE)
FOR XML PATH('PARAMETERS'),TYPE)
FROM @Subscription s
INNER JOIN @Parameters p
ON p.ID_Subscription=s.ID
FOR XML PATH('SUBSCRIPTION'),ROOT('SUBSCRIPTIONS')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Here is how I got it to work in the end:
SELECT[SubscriptionName] =s.Name
,[DeliveryMethod] =s.DeliveryMethod
-- extensions
,(SELECT[Name] =eupvt.Name
,[Value] =eupvt.Value
FROM(SELECT ie.[To], ie.Priority, ie.[Subject], ie.Body FROM @Extension ie WHERE ie.ID = e.ID) a
UNPIVOT (Value FOR Name IN ([To], [Priority], [Subject], [Body])) eupvt
FOR XML RAW('extension'), ROOT('extensions'), TYPE
)
-- parameters
,(SELECT[Name] =punpvt.Name
,[Value] =punpvt.Value
FROM(SELECT [Param1], [Param2], [Param3] FROM @Parameters ip WHERE ip.ID = p.ID) b
UNPIVOT(Value FOR Name IN (Param1, Param2, Param3)) punpvt
FOR XML RAW('parameter'), ROOT('parameters'), TYPE
)
FROM @Subscription s
INNER JOIN @Extension e ON s.ID = e.ID_Subscription
INNER JOIN @Parameters p ON s.ID = p.ID_Subscription
FOR XML RAW('subscription'), ROOT('subscriptions'), TYPE;
Thanks for the feedback
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply