Create xml with attribute value in xml format

Question

How can values from a column in a table which is already in XML format be created as a value for an attribute in XML and retain its XML format (brackets, quotes, etc.)? (It seems that the term for what is happening is that the characters (<, >, ") are being entitized.) (SQL SERVER 2008 R2)

declare @T table (
ID int identity,
Col1 varchar(200),
Col2 varchar(max)
);

insert @T
select 'ABC', 'Value01'
union
select 'DEF', '<Ele01>A01</Ele01>'
union
select 'GHI', '<Elements><Ele02>A02</Ele02><Ele03>A03</Ele03></Elements>'
union
select 'JKL', '<Ele04 Att01="V01" Att02="V02" />';

select (
    select
        Col1 as '@Col1',
        Col2 as '@Col2'
    from @T
    for xml path(N'Item'), type
    ) as TValues
for xml path(N'T'), type, elements xsinil;

The result should look like:

<T xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <TValues>
    <Item Col1="ABC" Col2="Value01" />
    <Item Col1="DEF" Col2="<Element01>A01</Element01>" />
    <Item Col1="GHI" Col2="<Elements><Element02>A02</Element02><Element03>A03</Element03></Elements>" />
    <Item Col1="JKL" Col2="<Element04 Att01="V01" Att02="V02" />" />
  </TValues>
</T>

This is the actual result:

<T xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <TValues>
    <Item Col1="ABC" Col2="Value01" />
    <Item Col1="DEF" Col2="&lt;Element01&gt;A01&lt;/Element01&gt;" />
    <Item Col1="GHI" Col2="&lt;Elements&gt;&lt;Element02&gt;A02&lt;/Element02&gt;&lt;Element03&gt;A03&lt;/Element03&gt;&lt;/Elements&gt;" />
    <Item Col1="JKL" Col2="&lt;Element04 Att01=&quot;V01&quot; Att02=&quot;V02&quot; /&gt;" />
  </TValues>
</T>

I have searched for an answer and these are some of the only links that I could find which deal with the issue somewhat: http://blogs.lobsterpot.com.au/2010/04/15/handling-special-characters-with-for-xml-path/ How do I avoid character encoding when using "FOR XML PATH"? https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

I understand that I can replace the entitized characters after the fact but I believe there must be a better way. I truly appreciate the help from someone who knows how to make this happen.

I apologize if my question fails to meet someone's standard.

Answer 1

As you were told already, your XML is not valid. It is easy to generate a string, which looks like XML, but this does not mean, that your string is valid XML.

I understand that I can replace the entitized characters after

If you do this, you have to do it on string level with REPLACE, but this will destroy your XML. No valid tool will be able to read this!

The first question is: Why are you storing this XML in a column VARCHAR(MAX)? XML is not just some silly text with fancy extras... You should make this column XML type. This will ensure, that your fragments are valid XML.

What you might do: You can place your fragments as valid sub-elements into your XML:

select (
    select
        Col1 as '@Col1',
        CAST(Col2 AS XML) as 'Col2'
    from @T
    for xml path(N'Item'), type
    ) as TValues
for xml path(N'T'), type, elements xsinil;

The result

<T xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <TValues>
    <Item Col1="ABC">
      <Col2>Value01</Col2>
    </Item>
    <Item Col1="DEF">
      <Col2>
        <Ele01>A01</Ele01>
      </Col2>
    </Item>
    <Item Col1="GHI">
      <Col2>
        <Elements>
          <Ele02>A02</Ele02>
          <Ele03>A03</Ele03>
        </Elements>
      </Col2>
    </Item>
    <Item Col1="JKL">
      <Col2>
        <Ele04 Att01="V01" Att02="V02" />
      </Col2>
    </Item>
  </TValues>
</T>