I am creating an xml blob out of concatenated string data successfully in sql
server 2008. I have been asked to now append results from another column that
is already XML data type to this same column. I figured I could just
concatenate the two data sources together and the two would just mesh. I keep
tripping over the " + " concat operator. How do I concat two xml blobs
together to make one big one?
Here is my original code:
select ItemAttributes=convert(xml, ISNULL(CAST('<CompanyName><![CDATA['
+sup.CompanyName+']]></CompanyName>' AS NVARCHAR(MAX)) ,'')
+ISNULL(CAST('<ProgramName><![CDATA[' +pc.name+']]></ProgramName>'
AS NVARCHAR(MAX)) ,'')
+ISNULL(CAST('<ProgramDesc><![CDATA['
+pc.Description+']]></ProgramDesc>' AS NVARCHAR(MAX)) ,'')
+ISNULL(CAST('<ProgramType><![CDATA['
+meti.MasterEntityName+']]></ProgramType>' AS NVARCHAR(MAX)) ,''),0)
from <tables>

This gives me a result set like this:
<CompanyName>xxx </CompanyName>
<ProgramName>Title</ProgramName>
<ProgramDesc>description</ProgramDesc>
<ProgramType>type</ProgramType>

for each title, I in turn store 1-5 xml blobs containing anything from links
to FAQs. I want to add the raw xml blob "as-is" to the end of my existing xml
blob. Here is my first stab:
select ItemAttributes=convert(xml, ISNULL(CAST('<CompanyName><![CDATA['
+sup.CompanyName+']]></CompanyName>' AS NVARCHAR(MAX)) ,'')
+ISNULL(CAST('<ProgramName><![CDATA[' +pc.name+']]></ProgramName>'
AS NVARCHAR(MAX)) ,'')
+ISNULL(CAST('<ProgramDesc><![CDATA['
+pc.Description+']]></ProgramDesc>' AS NVARCHAR(MAX)) ,'')
+ISNULL(CAST('<ProgramType><![CDATA['
+meti.MasterEntityName+']]></ProgramType>' AS NVARCHAR(MAX)) ,''),0)
+bl.XMLBlob
from <tables>
here is error I get:
Msg 8117, Level 16, State 1, Line 5
Operand data type xml is invalid for add operator.

Like I said, all I want to do is append bl.xmlblob to the end of my existing
manually created xml blob.
Any advise would be greatly appreciated!!!
thanks!

You can combine XML like this:

DECLARE @xml1 XML, @xml2 XML, @xml3 XML

SET @xml1 = '<yourXML1/>'
SET @xml2 = '<yourXML2/>'

-- Combine the two xml
SET @xml3 =
(
SELECT @xml1 AS "*", @xml2 AS "*"
FOR XML PATH('')
)

SELECT @xml3

Not sure if that will work for your example / CDATA. Give it a try, let me
know how you get on.

"Carl Henthorn" wrote: