SQL Server FOR XML row combine rows

Question

I was originally trying to do this in C# and have ditched that path for a query. I have a table (SiteTest) with a column called "SiteID" and another column in there is called "Services"

I then create some temp tables and use cross apply on the "Services" column to get:

I am now trying to use the FOR XML:

SELECT
   ProviderName
   ,SiteID
   ,SiteName
   ,(
       SELECT
         Services AS [CoveredServiceCode]
       FROM
         ProviderSiteTestTable AS [CoveredService]
         FOR XML AUTO, TYPE, ELEMENTS, ROOT('CoveredServices')
   )
FROM
    ProviderSiteTestTable AS [ProviderSite]
    FOR XML AUTO, TYPE, ELEMENTS, ROOT('ProviderSites')

Then I get:

<ProviderSites>
    <ProviderSite>
        <ProviderName>Health Center USA</ProviderName>
        <SiteID>15</SiteID>
        <SiteName>Texas Behavioral Health LLC</SiteName>
        <CoveredServices>
          <CoveredService>04</CoveredService>
        </CoveredServices>
    </ProviderSite>
    <ProviderSite>
        <ProviderName>Health Center USA</ProviderName>
        <SiteID>15</SiteID>
        <SiteName>Texas Behavioral Health LLC</SiteName>
        <CoveredServices>
          <CoveredService>08</CoveredService>
        </CoveredServices>
    </ProviderSite>
    ....
    ....
</ProviderSites>

Result I am trying to get (all "CoveredServices" for that site under that site):

<ProviderSites>
    <ProviderSite>
        <ProviderName>Health Center USA</ProviderName>
        <SiteID>15</SiteID>
        <SiteName>Texas Behavioral Health LLC</SiteName>
        <CoveredServices>
          <CoveredService>04</CoveredService>
          <CoveredService>08</CoveredService>
          ....
        </CoveredServices>
    </ProviderSite>
    ....
    ....
</ProviderSites>

UPDATE Looks like doing a group by might be what I am looking for, testing this now

Answer 1

You can try this:

DECLARE @mockup TABLE(ID INT, ProviderID VARCHAR(100),ProviderName VARCHAR(100),SiteID INT,SiteName VARCHAR(100),OpenDate DATE,[Services] VARCHAR(10));

INSERT INTO @mockup VALUES
 (1,'12-000000','Health Center USA',15,'Texas blah','20120107','04')
,(1,'12-000000','Health Center USA',15,'Texas blah','20120107','08')
,(2,'12-000000','Health Center USA',15,'Texas blah','20120107','ab')
,(2,'12-000000','Health Center USA',15,'Texas blah','20120107','cd');

SELECT tbl.ProviderName
      ,tbl.ID
      ,tbl.SiteName
      ,(
        SELECT tbl2.[services] AS CoveredService
        FROM @mockup tbl2
        WHERE tbl2.ID=tbl.ID --correlated sub-query
        FOR XML PATH(''),TYPE
      ) AS CoveredServices
FROM @mockup tbl
GROUP BY tbl.ProviderName,tbl.ID,tbl.SiteName --Control the final Site-Order here...
FOR XML PATH('ProviderSite'),ROOT('ProviderSites');

The result

<ProviderSites>
  <ProviderSite>
    <ProviderName>Health Center USA</ProviderName>
    <ID>1</ID>
    <SiteName>Texas blah</SiteName>
    <CoveredServices>
      <CoveredService>04</CoveredService>
      <CoveredService>08</CoveredService>
    </CoveredServices>
  </ProviderSite>
  <ProviderSite>
    <ProviderName>Health Center USA</ProviderName>
    <ID>2</ID>
    <SiteName>Texas blah</SiteName>
    <CoveredServices>
      <CoveredService>ab</CoveredService>
      <CoveredService>cd</CoveredService>
    </CoveredServices>
  </ProviderSite>
</ProviderSites>

Some explanation

In most cases FOR XML PATH is the best choice. With AUTO mode you are allowing the engine to decide the best output. I prefer approaches, where the developer can control everything by hand.

I use a GROUP BY on the outer SELECT. This will reduce the set to 1-row-per-ID. Then I use a correlated sub-query to create the internal XML per ID.

Some comments

It is a bad idea, to store more than one value per column. If you can change this, try to use a m:n model:

• table Provider
• table Services
• mapping table ProviderServices with foreign keys to both sides.

It looks as if the date OpenDate and the ClosedDate were string columns. This is very dangerous, especially with a culture dependant format (7/1/2012) will be the 7th of January or the first of July, depending on your system's culture settings... My code assumes the 7th of January...

For your next question: Please do not post pictures, we do not want to type this in. See my example how I created a mcve. A stand-alone (ready to execute) sample with just as much data as you need to explain the principles.

UPDATE

In your case you might include the string splitting into the query directly. Try this

DECLARE @mockup TABLE(ID INT, ProviderID VARCHAR(100),ProviderName VARCHAR(100),SiteID INT,SiteName VARCHAR(100),OpenDate DATE,[Services] VARCHAR(100));

INSERT INTO @mockup VALUES
    (1,'12-000000','Health Center USA',15,'Texas blah','20120107','04,08,01,02')
,(2,'12-000000','Health Center USA',15,'Texas blah','20120107','04,08,02,12')

SELECT tbl.ProviderName
        ,tbl.ID
        ,tbl.SiteName
        ,CAST('<CoveredService>' + REPLACE([services],',','</CoveredService><CoveredService>') + '</CoveredService>' AS XML) AS CoveredServices
FROM @mockup tbl
FOR XML PATH('ProviderSite'),ROOT('ProviderSites');