replace text inside sql with XML output

Question

I have this query:

SELECT [content_id]
      ,[content_html]
      ,[date_created]
      ,folder_id
  FROM content ct 

where folder_id=126
order by content_title
  FOR XML PATH('PressRelease'), ROOT ('PressReleases')

When I run this query this is the XML file that is generated:

<PressReleases>
  <PressRelease>
    <content_id>6442452927</content_id>
    <content_html>&lt;root&gt;&lt;Date&gt;2015-12-02&lt;/Date&gt;
        &lt;Description&gt;&lt;p class="customHeader"&gt;jobs to Philadelphia.&lt;/p&gt;
        &lt;p&gt;mtext in here.&lt;/p&gt;
        &lt;p&gt;mtext in here.&lt;/p&gt;
        &lt;/Description&gt;
        &lt;SEO&gt;&lt;h1&gt;Pennsylvania Location&lt;/h1&gt;
        &lt;div class="bulletRightBar"&gt; The move was made possible in part by the Philadelphia Jobs 
        Credit&lt;/div&gt;
&lt;/SEO&gt;
&lt;/root&gt;</content_html>
    <date_created>2015-12-02T09:47:12</date_created>
    <folder_id>126</folder_id>
  </PressRelease>
  <PressReleases>

What I need is this XML file:

<PressReleases>
  <PressRelease>
    <content_id>6442452927</content_id>
    <content_html><root><Date>2015-12-02</Date>
        <Description>&lt;p class="customHeader"&gt;jobs to Philadelphia.&lt;/p&gt;
        &lt;p&gt;mtext in here.&lt;/p&gt;
        &lt;p&gt;mtext in here.&lt;/p&gt;
        </Description>
        <SEO>&lt;h1&gt;Pennsylvania Location&lt;/h1&gt;
        &lt;div class="bulletRightBar"&gt; The move was made possible in part by the Philadelphia Jobs 
        Credit&lt;/div&gt;
</SEO>
</root></content_html>
    <date_created>2015-12-02T09:47:12</date_created>
    <folder_id>126</folder_id>
  </PressRelease>
  <PressReleases>

Inside the <content_html> I want to make <root> <date> and <Description> as XML elements but leave the rest as encoded html.

Here's a screenshot for the sql result

Answer 1

It's not pretty, but you could cast the XML field as a string, use the REPLACE function and cast it back to XML like below. You might want to create a function that does this as the line would get with a lot of replacing:

SELECT [content_id]
      ,cast(REPLACE(cast([content_html] as varchar(max)),'&lt;root&gt;','<root>') as xml)
      ,[date_created]
      ,folder_id
  FROM content ct 

where folder_id=126
order by content_title
  FOR XML PATH('PressRelease'), ROOT ('PressReleases')

or here is a way to call it with a function

CREATE FUNCTION [dbo].[XML_Replace]
        (@XML_Field XML)
RETURNS XML 

BEGIN
 DECLARE @xml varchar(max) 
 DECLARE @xml_Mid varchar(max)
 DECLARE @strtBigInt bigint
 , @endBigInt bigint

 SET @xml = cast(@XML_Field as varchar(max))
 SET @strtBigInt = CHARINDEX('<Description>',@xml)
 SET @endBigInt = CHARINDEX('</SEO>',@xml)

 SET @xml_Mid = SUBSTRING(@xml, @strtBigInt+19,@endBigInt-@strtBigInt-19);

 RETURN(cast(REPLACE(REPLACE(REPLACE(REPLACE(substring(@xml,0,@strtBigInt+19),'&lt;','<'),'&gt;','>') +    @xml_Mid +  REPLACE(REPLACE(substring(@xml,@endBigInt,Len(@xml)),'&lt;','<'),'&gt;','>'),'&lt;/Description&gt;','</Description>'),'&lt;SEO&gt;','<SEO>') as xml));
END

Then use the funtion in your code:

SELECT [content_id]
      ,dbo.XML_Replace([content_html]) as content_html
      ,[date_created]
      ,folder_id
  FROM content ct 

where folder_id=126
order by content_title
  FOR XML PATH('PressRelease'), ROOT ('PressReleases')