CODEWITHSUNDEEP
sql-serverxmlsql-server-2008-r2for-xml-path
Erg
Erg

Reputation: 69

Create xml with attribute value in xml format

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.

Upvotes: 1

Views: 112

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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>

Upvotes: 0

kjhughes
kjhughes

Reputation: 111501

Your "should look like" XML is not XML at all -- that's why your tools are not helping you. No conformant XML processor will help you generate non-well-formed XML. You need to fix your XML design so that markup does not appear in attribute values, or you need to deal with the escaped version that your tools are providing you.

If you say you can't fix your XML design or deal with the escaped characters in your attribute values, then you have to live without the support of XML libraries and tools because they're not going to help you with your "should look like" format. Prepare to operate at the level of character streams, and build up your own toolset. Also prepare the consumers of your data that they won't be able to use XML tools either.

Bottom line: Don't generate non-well-formed XML.

Upvotes: 1

Related Questions