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><root><Date>2015-12-02</Date>
<Description><p class="customHeader">jobs to Philadelphia.</p>
<p>mtext in here.</p>
<p>mtext in here.</p>
</Description>
<SEO><h1>Pennsylvania Location</h1>
<div class="bulletRightBar"> The move was made possible in part by the Philadelphia Jobs
Credit</div>
</SEO>
</root></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><p class="customHeader">jobs to Philadelphia.</p>
<p>mtext in here.</p>
<p>mtext in here.</p>
</Description>
<SEO><h1>Pennsylvania Location</h1>
<div class="bulletRightBar"> The move was made possible in part by the Philadelphia Jobs
Credit</div>
</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.

</Description>and<SEO>as encoded HTML or convert them as well