FOR XML PATH in Sql Server

sql-server for-xml

19,524

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")

19,524

Keith Barrows

Keith lives in Florida and specializes in Information Technology applications utilizing web technologies. He has been working in software ever since high school and stepped forward as a professional in the early 1990s. He is very good at figuring out new things on the fly. Technology is always changing. What is hot today is a memory tomorrow. Realizing this early on he spent his time becoming a Self-Sufficient Developer, somebody who can learn new things as they arise. He has demonstrated a passion to be highly proficient in any project he tackles. With over 20 years of experience Keith has consulted on all aspects of the software development life cycle from design and development to quality assurance and maintenance. He has worked in both the Agile and Water Fall methodologies of software creation, to include Scrum, Kan-ban and XP. Keith has a broad set of skills in the web sphere from light UI design to a deeper server-side knowledge of .NET including Core, Entity Framework, Web Forms, MVC, Web API, C#, VB as well as T-SQL and NoSql. Some of Keith's highlights include: Designed and built a Web Forms based system to replace a highly manual and error prone process that ended up saving the client over $10 million in governmental fines. Worked on updating a legacy system that could no longer handle the client load allowing the system to go from 100 clients with 100 users each to 500 clients with 250 users each. Experience leading 3 to 10 member development teams. Volunteered to be part of a 4 man team to self lead the development teams consisting of 26 developers and 8 QA personnel as the company was missing a CTO. Currently, Keith is an independent consultant with 20+ years of industry experience actively pursuing an even deeper understanding of .NET, cloud based security and web development. He is active on Stack Overflow, was a former ASP.NET MVP and a founding member of the ASPInsiders.

Updated on April 16, 2022

Comments

Keith Barrows about 2 years

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>&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

Keith Barrows almost 15 years

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!
Remus Rusanu almost 15 years

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

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