title: How to encode XML in T SQL without the additional XML overhead
body: <p>I have a database which (For whatever reason) has a column containing pipe delimited data.</p>
  
  <p>I want to parse this data quickly, so I've thought of converting this column (nvarchar) into an XML by replacing the pipes with XML attributes and putting it into an XML data typed column somewhere else.</p>
  
  <p>It works, except in the case where that column had a character that required encoding, such a '&lt;' character.</p>
  
  <p>I found I could encode XML using FOR XML clause, however, that appears to inject some XML tags around the data.</p>
  
  <p>For example: (this gives error on bad character)</p>
  
  <pre><code>SELECT CAST('&lt;f&gt;' + replace(value,'|','&lt;/f&gt;&lt;f&gt;') + '&lt;/f&gt;' AS XML)
  FROM TABLE
  </code></pre>
  
  <p>this gives xml encoded value, but wraps it in "&lt; value> &lt; /value>" tag</p>
  
  <pre><code>  SELECT value
     FROM table
    FOR XML PATH('')
  </code></pre>
  
  <p>Any ideas on how I can get the XML encoded value without this extra tag added,  so I can convert the pipe format to XML after it's done (preferably in one swoop)?</p>
  
  <p>EDIT: since people are asking, this is what 5 potential rows of data might look like</p>
  
  <pre><code>foo
  foo|bar
  foo|bar|1
  foo||
  baz|
  </code></pre>
  
  <p>And the results would be</p>
  
  <pre><code>Col1, Col2, Col3
  foo,null,null
  foo,bar,null
  foo,bar,1
  foo,null,null
  baz,null,null
  </code></pre>
  
  <p>I'm achieving this by using the resulting XML type in a sub query such as: (it can be up to 4 columns pr 3 pipes in any given row)</p>
  
  <pre><code>    SELECT 
            *,
           x.query('f[1]').value('.','nVarChar(2048)')  Col1
          ,x.query('f[2]').value('.','nVarChar(2048)')  Col2
          ,x.query('f[3]').value('.','nvarchar(2048)')  Col3
          ,x.query('f[4]').value('.','nvarchar(2048)')  Col4
      FROM
      (
          SELECT *,
          CAST('&lt;f&gt;' + REPLACE(Value,'|','&lt;/f&gt;&lt;f&gt;') + '&lt;/f&gt;' AS XML) as x
       FROM  table
      ) y
  </code></pre>
  
  <p>@srutzky makes a great point. No, I do not need to do XML here at all. If I can find a fast &amp; clean way to parse pipes in a set based operation, I'll do that. Will review the SQL# documentation...</p>