FOR XML PATH in Sql Server
NOTE: I have solved the majority of this problem but have run into a snag. Read to the bottom please. You will see where I added a (NOTE) section. TIA.
I have a rather extensive join query that I want dumped to XML. I have it almost working but I am missing a concept in here somewhere. My query (abbreviated) looks like:
SELECT Campaign.CampaignId "Campaign/ID"
, Campaign.CompanyId "Campaign/CompanyID"
, Campaign.CampaignName "Campaign/Name"
...
, Audio.AudioID "Campaign/Audio/ID"
, Audio.[Name] "Campaign/Audio/Name"
...
, Video.CampaignVideosAudioMute "Campaign/Video/Audio/Mute"
, Video.CampaignVideosAudioVolume "Campaign/Video/Audio/Volume"
, Video.CampaignVideosPositionX "Campaign/Video/Position/X"
...
, Characters.CharacterID "Campaign/Characters/Character/ID"
, Characters.[Name] "Campaign/Characters/Character/Name"
...
, Element.ElementID "Campaign/Elements/Element/ID"
, Element.Editable "Campaign/Elements/Element/Editable"
...
, [Image].ImageID "Campaign/Elements/Element/Image/ID"
, [Image].[Path] "Campaign/Elements/Element/Image/Path"
...
, [Text].TextID "Campaign/Elements/Element/Text/ID"
, [Text].Value "Campaign/Elements/Element/Text/Value"
FROM vwCampaign Campaign
LEFT JOIN dbo.vwCampaignAudio Audio ON Campaign.CampaignId = Audio.CampaignId
LEFT JOIN dbo.vwCampaignCharacters Characters ON Campaign.CampaignId = Characters.CampaignId
LEFT JOIN dbo.vwCampaignVideo Video ON Campaign.CampaignId = Video.CampaignId
LEFT JOIN dbo.vwCampaignElements Element ON Campaign.CampaignId = Element.CampaignId
LEFT JOIN dbo.vwCampaignElementImage [Image] ON Element.CampaignId = [Image].CampaignId AND Element.ElementID = [Image].ElementID
LEFT JOIN dbo.vwCampaignElementText [Text] ON Element.CampaignId = [Text].CampaignId AND Element.ElementID = [Text].ElementID
WHERE Campaign.CampaignId = 10370
FOR XML PATH, ELEMENTS XSINIL
The way the data works I have:
- 1 campaign row
- 1 audio row - related to the campaign row
- 1 video row - related to the campaign row
- 1-n character rows - related to the campaign row
- 1-n element rows - related to the campaign row
- 0 or 1 image rows - related to each element row
- 0 or 1 text rows - related to each element row
The XML is coming out like:
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Campaign>
<ID>10370</ID>
<CompanyID>C2811EA3-361A-411A-BB4C-816A5D6C12DB</CompanyID>
<Name>Keith01</Name>
<URL>http://kab.rivworks.com/tests/kab02.htm</URL>
<Module>Coupon</Module>
<StartDate>2009-06-29T12:05:00</StartDate>
<EndDate>2021-06-30T18:00:00</EndDate>
<Notes>Test #1</Notes>
<Meta>D7E7D735-8D64-4127-84B1-7D72FB5EDD17</Meta>
<Orientation>Half-Body Left</Orientation>
<PresentationPlayerFlashVars>config=http://cdn1.deal4it.com/rivworks/demos/skymall/skymall-coupon-3.xml</PresentationPlayerFlashVars>
<Player>
<CookieIdentity>honirjymcvzk</CookieIdentity>
<Stage>
<Top></Top>
<Left></Left>
<Height>423</Height>
<Width>500</Width>
<MarginLeft></MarginLeft>
<Container>
<Background>
<Color>0xffffff</Color>
<Image></Image>
</Background>
</Container>
</Stage>
</Player>
<Audio>
<ID xsi:nil="true" />
<Name xsi:nil="true" />
<Path xsi:nil="true" />
<Meta xsi:nil="true" />
<Genre xsi:nil="true" />
</Audio>
<Video>
<ControlbarEvent>visible</ControlbarEvent>
<Event>play</Event>
<PresentationSkin>https://widgets.rivworks.com/player/latest/rivplayer.swf</PresentationSkin>
<Audio>
<Mute>False</Mute>
<Volume>100</Volume>
</Audio>
<Position>
<X>19</X>
<Y>140</Y>
</Position>
<About>
<Text>RIV Works</Text>
<Url>http://www.deal4it.com</Url>
</About>
<Size>
<Height>266</Height>
<Width>400</Width>
</Size>
<Settings>
<Autostart>True</Autostart>
<Buffer>1</Buffer>
<DelayPlay>0</DelayPlay>
<Item>0</Item>
<Quality>True</Quality>
<Repeat>none</Repeat>
</Settings>
</Video>
<Character>
<ID>19029FFC-C1C0-4134-B813-93A9FF17C7F6</ID>
<Name>Jenna</Name>
<Actor>CD5AF2B6-C39A-4316-BFB0-D4450194EC80</Actor>
<Meta>10041662-305F-4493-ACB3-460D687306A4</Meta>
<Access>Public</Access>
<Configuration>Individual</Configuration>
<ImageThumbnail>http://cdn1.deal4it.com/rivworks/images/headshots/jenna.jpg</ImageThumbnail>
<isPublic>1</isPublic>
<Demographics>
<Age>31 - 40</Age>
<Ethnicity>Caucasian</Ethnicity>
<Gender>Female</Gender>
</Demographics>
</Character>
<Elements>
<Element>
<ID>D9B2A643-73EC-4D55-BA34-D643113CEDEA</ID>
<Editable>1</Editable>
<Meta>D5F6175C-8DC7-4F18-9A5F-E2021579498B</Meta>
<Position>
<Level>2</Level>
<X>464</X>
<Y>21</Y>
</Position>
<Image>
<ID>90FF7F5A-75EC-4FB5-81B1-B9BEC4E8A22A</ID>
<Path>http://developer.rivworks.com/images/a5b19fe8-c8d3-4588-9eac-7cdf39b52078.jpg</Path>
<Link></Link>
<Target></Target>
<Meta>97261982-2131-41F7-9E2C-ADB10E31ED20</Meta>
<Size>
<Height>16</Height>
<Width>16</Width>
</Size>
</Image>
<Text>
<ID xsi:nil="true" />
<Value xsi:nil="true" />
<Link xsi:nil="true" />
<Target xsi:nil="true" />
<Meta xsi:nil="true" />
<FontColor xsi:nil="true" />
<FontFamily xsi:nil="true" />
<FontSize xsi:nil="true" />
</Text>
</Element>
</Elements>
</Campaign>
</row>
Unfortunately I have a few problems with this.
- The root element is still at the row level. The root element should be
- If I have 3 characters and 3 elements I end up with 9 root elements. The only thing that changes from one root element to the next is which character and which element is showing. (And compound this with each element having 0 or 1 texts and/or images)
The XML should come out looking something like:
<campaign>
<ID>10370</ID>
<CompanyID>C2811EA3-361A-411A-BB4C-816A5D6C12DB</CompanyID>
<etc>...</etc>
<Characters>
<Character>
<data>...</data>
<Character>
<Character>
<data>...</data>
<Character>
</Characters>
<Elements>
<Element>
<data>...</data>
<Image>...</Image>
<Text>...</Text>
<Element>
<Element>
<data>...</data>
<Image>...</Image>
<Text>...</Text>
<Element>
</Elements>
</campaign>
What do I need to change? DO I need to look at a different method of creating my XML, maybe some sort of nesting clause?
NOTE: After playing around and a lot of googling/binging I have altered my query so it uses nested queries. Here is what it looks like now:
SELECT Campaign.CampaignId "Campaign/ID"
, Campaign.CompanyId "Campaign/CompanyID"
, Campaign.CampaignName "Campaign/Name"
...
, Audio.AudioID "Campaign/Audio/ID"
, Audio.[Name] "Campaign/Audio/Name"
...
, Video.CampaignVideosControlbarEvent "Campaign/Video/ControlbarEvent"
, Video.CampaignVideosEvent "Campaign/Video/Event"
, (SELECT cc.CharacterID "Character/ID"
, cc.[Name] "Character/Name"
FROM dbo.vwCampaignCharacters cc
WHERE cc.CampaignID = Campaign.CampaignId
FOR XML PATH ('')
) AS "Campaign/Characters"
, (SELECT ce.ElementID "Element/ID"
, ce.Editable "Element/Editable"
, ce.Meta "Element/Meta"
, ce.PositionLevel "Element/Position/Level"
, ce.PositionX "Element/Position/X"
, ce.PositionY "Element/Position/Y"
, (SELECT cei.ImageID "Image/ID"
, cei.[Path] "Image/Path"
, cei.Link "Image/Link"
, cei.Target "Image/Target"
, cei.Meta "Image/Meta"
, cei.SizeHeight "Image/Size/Height"
, cei.SizeWidth "Image/Size/Width"
FROM dbo.vwCampaignElementImage cei
WHERE cei.CampaignID = ce.CampaignId
AND cei.ElementID = ce.ElementID
FOR XML PATH ('')
) AS "Element"
, (SELECT cet.TextID "ID"
, cet.Value "Value"
, cet.Link "Link"
, cet.Target "Target"
, cet.Meta "Meta"
, cet.FontColor "FontColor"
, cet.FontFamily "FontFamily"
, cet.FontSize "FontSize"
FROM dbo.vwCampaignElementText cet
WHERE cet.CampaignID = ce.CampaignId
AND cet.ElementID = ce.ElementID
FOR XML PATH ('Text')
) AS "Element"
FROM dbo.vwCampaignElements ce
WHERE ce.CampaignID = Campaign.CampaignId
FOR XML PATH ('Element')
) AS "Campaign/Elements"
FROM vwCampaign Campaign
LEFT JOIN dbo.vwCampaignAudio Audio ON Campaign.CampaignId = Audio.CampaignId
LEFT JOIN dbo.vwCampaignVideo Video ON Campaign.CampaignId = Video.CampaignId
WHERE Campaign.CampaignId = 10370
FOR XML PATH ('Campaign'), ROOT ('Campaigns'), ELEMENTS XSINIL
The XML is coming out almost perfect now except for the markup of the sub-queries.
<Campaigns xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Campaign>
<Campaign>
<ID>10370</ID>
<CompanyID>C2811EA3-361A-411A-BB4C-816A5D6C12DB</CompanyID>
<Name>Keith01</Name>
...
<Characters><Character><ID>19029FFC-C1C0-4134-B813-93A9FF17C7F6</ID><Name>Jenna</Name> ...
<Elements><Element><Element><ID>D9B2A643-73EC-4D55-BA34-D643113CEDEA</ID><Editable>1</Editable> ...
</Campaign>
</Campaign>
</Campaigns>
The Character and Element sub-queries are producing < / > instead of < / >, how do you say, URL safe markup? I don't want this.
The Image and Text sub-sub-queries are producing &< / &> instead of < / >. Notice that it has been made URL safe twice! I don't want this either.
Any idea of how to get the actual markup instead of this crap. :)
TIA
Answer
You should probably transfer the 'Campaign' into the PATH:
SELECT Campaign.CampaignId "ID"
, Campaign.CompanyId "CompanyID"
, Audio.AudioID "Audio/ID"
, Audio.[Name] "Audio/Name"
...
FROM vwCampaign Campaign
LEFT JOIN dbo.vwCampaignAudio Audio
...
WHERE Campaign.CampaignId = 10370
FOR XML PATH('Campaign'), ELEMENTS XSINIL
Update
For the suqueries problem, you need to use the FOR XML PATH(..),TYPE to create a typed XML value (as opposed to a string containing xml). An XML typed subquery will create an XML element, a string will just insert the result as text() and will be escaped.
select a, (select b from t for xml path("b"), type) as "*"
from ... for xml path("a")