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"&gt;
  <Campaign>
    <ID>10370</ID>
    <CompanyID>C2811EA3-361A-411A-BB4C-816A5D6C12DB</CompanyID>
    <Name>Keith01</Name>
    <URL>http://kab.rivworks.com/tests/kab02.htm&lt;/URL&gt;
    <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&lt;/PresentationPlayerFlashVars&gt;
    <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&lt;/PresentationSkin&gt;
      <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&lt;/Url&gt;
      </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&lt;/ImageThumbnail&gt;
      <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&lt;/Path&gt;
          <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"&gt;
  <Campaign>
    <Campaign>
      <ID>10370</ID>
      <CompanyID>C2811EA3-361A-411A-BB4C-816A5D6C12DB</CompanyID>
      <Name>Keith01</Name>
...
      <Characters>&lt;Character&gt;&lt;ID&gt;19029FFC-C1C0-4134-B813-93A9FF17C7F6&lt;/ID&gt;&lt;Name&gt;Jenna&lt;/Name&gt; ...
      <Elements>&lt;Element&gt;&lt;Element&gt;&lt;ID&gt;D9B2A643-73EC-4D55-BA34-D643113CEDEA&lt;/ID&gt;&lt;Editable&gt;1&lt;/Editable&gt; ...
    </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 &amp< / &amp> 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

Thanks for that. I added it in as well as a ROOT attribute (in case I do not select a single campaign). That solves one of my many challenges on this one!

Keith Barrows 2009-09-04 23:04:02

It was about time I learn the FOR XML has a ROOT option lol. Thanks for that.

Remus Rusanu 2009-09-04 23:07:31

See update in my post for the subquery XML issue

Remus Rusanu 2009-09-04 23:11:40

+1 exactly: the nested XML subqueries need to use **FOR XML PATH(), TYPE** to produce nested XML elements

marc_s 2009-09-05 12:37:41

I totally missed the "TYPE" piece - thanks!!

Keith Barrows 2009-09-08 16:44:13

ansaurus

tags:

views:

answers:

FOR XML PATH in Sql Server

related questions