title: SQL query - ntext query (for nodes, substring)
body: <p>I am querying ReportServer database where SSRS metadata is stored in a field called <code>Subscriptions.ExtensionSettings</code> datatype <code>NTEXT</code>.  The data values looks like XML-style text with <code>[Name] and [Value] tags</code> such as: <code>&lt;Name&gt;TO&lt;/Name&gt;</code>, <code>&lt;Name&gt;CC&lt;/Name&gt;</code>, <code>&lt;Name&gt;RenderFormat&lt;/Name&gt;</code>, and <code>&lt;Value&gt;Some text data here&lt;/Value&gt;</code>.  The text Values for each Name tag will sometimes have a line break.   </p>
  
  <blockquote>
    <p>Is there a SELECT statement that can query the NTEXT field to pull the 
    separate field values out for each record-- [TO], [CC], [RenderFormat]?</p>
  </blockquote>
  
  <p>In my search to this answer I have looked at these posts: <a href="https://stackoverflow.com/questions/384923/sql-query-grouping-by-an-ntext-field">SQL Query: Grouping by an ntext field</a>; <a href="https://stackoverflow.com/questions/36123601/ntext-text-image-datatypes-in-sql-server-2016-ctp-3">ntext/text/image datatypes in SQL server 2016 CTP 3</a></p>
  
  <p><strong>Sample data...</strong></p>
  
  <pre><code>DECLARE @TableData as table (
      DeliveryExtension nvarchar(260), ExtensionSettings ntext
      )
  
  INSERT INTO @TableData VALUES
      ('Report Server Email', '&lt;ParameterValues&gt;&lt;ParameterValue&gt;&lt;Name&gt;TO&lt;/Name&gt;&lt;Value&gt;name.one@companyinc.com; name.two@companyinc.com; name.3@companyinc.com&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;CC&lt;/Name&gt;&lt;Value&gt;name.four@companyinc.com; name.five@companyinc.com&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;IncludeReport&lt;/Name&gt;&lt;Value&gt;True&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;RenderFormat&lt;/Name&gt;&lt;Value&gt;EXCELOPENXML&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;Subject&lt;/Name&gt;&lt;Value&gt;@ReportName was executed at @ExecutionTime&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;Comment&lt;/Name&gt;&lt;Value&gt;Any undefined category is currently being addressed.&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;IncludeLink&lt;/Name&gt;&lt;Value&gt;False&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;Priority&lt;/Name&gt;&lt;Value&gt;NORMAL&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;/ParameterValues&gt;')
      ,('Report Server Email', '&lt;ParameterValues&gt;&lt;ParameterValue&gt;&lt;Name&gt;TO&lt;/Name&gt;&lt;Value&gt;name.one@companyinc.com; name.two@companyinc.com&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;CC&lt;/Name&gt;&lt;Value&gt;name.three@companyinc.com&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;IncludeReport&lt;/Name&gt;&lt;Value&gt;True&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;RenderFormat&lt;/Name&gt;&lt;Value&gt;EXCELOPENXML&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;Subject&lt;/Name&gt;&lt;Value&gt;@ReportName was executed at @ExecutionTime&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;Comment&lt;/Name&gt;&lt;Value&gt;@ReportName
  
  If there any issues with this report please open a ticket for Reporting Services Portal support.&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;IncludeLink&lt;/Name&gt;&lt;Value&gt;False&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;Priority&lt;/Name&gt;&lt;Value&gt;NORMAL&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;/ParameterValues&gt;')
      ,('Report Server FileShare', '&lt;ParameterValues&gt;&lt;ParameterValue&gt;&lt;Name&gt;PATH&lt;/Name&gt;&lt;Value&gt;\\network1234\c$\temp&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;FILENAME&lt;/Name&gt;&lt;Value&gt;AE Points Report&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;FILEEXTN&lt;/Name&gt;&lt;Value&gt;True&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;USERNAME&lt;/Name&gt;&lt;Value&gt;OCSgOkfVICihDFLe9g/oVGCa86jtJJLh2vqiuOYEvtYL1kudnJ6RuGqXlMpxqDz9&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;PASSWORD&lt;/Name&gt;&lt;Value&gt;f0zrUzblRan6Q9h9iW8FwevA9UKSo3guHz79rx3XG9i+OdAj3LxLaf659MJT0ZjE&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;RENDER_FORMAT&lt;/Name&gt;&lt;Value&gt;EXCELOPENXML&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;WRITEMODE&lt;/Name&gt;&lt;Value&gt;Overwrite&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;ParameterValue&gt;&lt;Name&gt;DEFAULTCREDENTIALS&lt;/Name&gt;&lt;Value&gt;False&lt;/Value&gt;&lt;/ParameterValue&gt;&lt;/ParameterValues&gt;')
  
  
  select 
      DeliveryExtension 
      , ExtensionSettings
  from @TableData
  </code></pre>
  
  <p><strong>Desired result...</strong></p>
  
  <pre><code>+-------------------------+--------------------------------------------------+----------------------------+--------------+
  | DeliveryExtension       | TO                                               | CC                         | RenderFormat |
  +-------------------------+--------------------------------------------------+----------------------------+--------------+
  | Report Server Email     | name.one@companyinc.com;                         | name.four@companyinc.com;  | EXCELOPENXML |
  |                         |   name.two@companyinc.com; name.3@companyinc.com |   name.five@companyinc.com |              |
  +-------------------------+--------------------------------------------------+----------------------------+--------------+
  | Report Server Email     | name.one@companyinc.com;                         | name.three@companyinc.com  | EXCELOPENXML |
  |                         |   name.two@companyinc.com                        |                            |              |
  +-------------------------+--------------------------------------------------+----------------------------+--------------+
  | Report Server FileShare |                                                  |                            | PDF          |
  +-------------------------+--------------------------------------------------+----------------------------+--------------+
  </code></pre>