Concatenate multiple xml columntype rows in SQL

I have an online form, whereby each entry adds the data as xml to an xml column in SQL.

ApplicationID(uniqueidentifier) | DateModified | ApplicationForm(XML)

What I need to do is perf开发者_开发百科orm a select query that will grab all the ApplicationForm xml values, and concatenate them together to form one result, e.g.

Row1: <ApplicationForm type=""></ApplicationForm>
Row2: <ApplicationForm type=""></ApplicationForm>

Select result:

<Applications>
   <ApplicationForm type=""></ApplicationForm>
   <ApplicationForm type=""></ApplicationForm>
</Applications>

---

Probably a better way to do it than this, but I specified the ApplicationForm tag as a tag to be removed, and then stripped it out.

DECLARE @a TABLE (ID UNIQUEIDENTIFIER,
                    DateModified DATETIME,
                    ApplicationForm XML)

INSERT INTO @a
        ( ID, DateModified, ApplicationForm )
VALUES  ( NEWID(), -- ID - uniqueidentifier
          '2010-12-07 18:47:36', -- DateModified - datetime
          '<Application><Form>123</Form></Application>'
          ) ,
         ( NEWID(), -- ID - uniqueidentifier
          '2010-12-07 18:47:36', -- DateModified - datetime
          '<Application><Form>456</Form></Application>'
          )             

DECLARE @Result VARCHAR(MAX)
SET @Result = CONVERT(VARCHAR(MAX), ( SELECT ApplicationForm AS "StripTagOut" 
FROM @a 
FOR XML PATH(''), ROOT('Applications'), TYPE   ))

SELECT CONVERT(xml, REPLACE(REPLACE(@Result, '</StripTagOut>', ''), '<StripTagOut>', ''))

---

This was an experiment in using XML EXPLICIT which I believe works:

SELECT 1 AS Tag,
       NULL AS Parent,
       NULL [Applications!1],
       NULL [ApplicationForm!2!!XMLTEXT]
UNION ALL
SELECT 2 AS Tag,
       1 AS Parent,
       NULL [Applications!1],
       ApplicationForm [ApplicationForm!2!!XMLTEXT]      
FROM YourTable
FOR XML EXPLICIT

For my own gratification, here is the sample script I used

CREATE TABLE XmlTest
(
   ApplicationForm xml
)

INSERT INTO XmlTest VALUES ('<ApplicationForm type="a"><SomeTag>SomeContent</SomeTag></ApplicationForm>')
INSERT INTO XmlTest VALUES ('<ApplicationForm type="b"><SomeTag>SomeOtherContent</SomeTag></ApplicationForm>')

SELECT 1 AS Tag,
       NULL AS Parent,
       NULL [Applications!1],
       NULL [ApplicationForm!2!!XMLTEXT]
UNION ALL
SELECT 2 AS Tag,
       1 AS Parent,
       NULL [Applications!1],
       ApplicationForm [ApplicationForm!2!!XMLTEXT]      
FROM XmlTest       
FOR XML EXPLICIT

Which output

<Applications>
  <ApplicationForm type="a">
    <SomeTag>SomeContent</SomeTag>
  </ApplicationForm>
  <ApplicationForm type="b">
    <SomeTag>SomeOtherContent</SomeTag>
  </ApplicationForm>
</Applications>

---

Take a look at this page, which lists quite a number of ways to concatenate rows of data.