Extracting values out of ntext

Question

So I have this select statement:

SELECT sql1.ExtensionSettings
FROM (SELECT *
      FROM [DBN-SERVER].[ReportServer].[dbo].[Subscriptions]
     ) sql1
WHERE report_oid = 'REPORT_ID' AND InactiveFlags = 0 AND
      (sql1.ExtensionSettings LIKE '%<Value>EXCELOPENXML</Value>%') OR
      (sql1.ExtensionSettings LIKE '%<Value>PDF</Value>%')

That statement will return 40 rows of this:

"["<ParameterValues><ParameterValue><Name>TO</Name><Value></Value></ParameterValue><ParameterValue><Name>CC</Name><Value></Value></ParameterValue><ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue><ParameterValue><Name>RenderFormat</Name><Value>EXCELOPENXML</Value></ParameterValue><ParameterValue><Name>Subject</Name><Value>AK Steel Daily Combined Order Status report for THE MILL STEEL CO-146159 (Excel version)</Value></ParameterValue><ParameterValue><Name>Comment</Name><Value>Attached is the daily order status report in Excel format</Value></ParameterValue><ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue><ParameterValue><Name>Priority</Name><Value>NORMAL</Value></ParameterValue></ParameterValues>"]"

I need to extract everything in the tags... I know there is nothing showing here but I had to remove the emails... does anyone know who I go about doing this for 40 rows?

Are you using SQL Server? If so, then please add that tag to your question. — Tim Biegeleisen, Aug 17 '18 at 14:15
I am using Sql Server and there is a tag on this question. Also, my OR does have parentheses... — , Aug 17 '18 at 14:44
Possible duplicate of [Select values from XML field in SQL Server 2008](https://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008) — Rubens Farias, Aug 17 '18 at 14:46
`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) — marc_s, Aug 17 '18 at 15:03

score 2 · Accepted Answer · answered Aug 17 '18 at 14:45

Your ntext value is an XML document, so you can go with something like this:

declare @data xml = '
    <ParameterValues>
       <ParameterValue>
          <Name>IncludeReport</Name>
          <Value>True</Value>
       </ParameterValue>
       <ParameterValue>
          <Name>RenderFormat</Name>
          <Value>EXCELOPENXML</Value>
       </ParameterValue>
       <ParameterValue>
          <Name>Subject</Name>
          <Value>
              AK Steel Daily Combined Order Status report for
              THE MILL STEEL CO-146159 (Excel version)
          </Value>
       </ParameterValue>
       <ParameterValue>
          <Name>Comment</Name>
          <Value>Attached is the daily order status report in Excel format</Value>
       </ParameterValue>
       <ParameterValue>
          <Name>IncludeLink</Name>
          <Value>False</Value>
       </ParameterValue>
       <ParameterValue>
          <Name>Priority</Name>
          <Value>NORMAL</Value>
       </ParameterValue>
    </ParameterValues>'

SELECT  c.value('(Name)[1]', 'nvarchar(max)') Name,
        c.value('(Value)[1]', 'nvarchar(max)') Value
FROM    @data.nodes('/ParameterValues/ParameterValue') t(c)

Eventually, you can add some row key in the output and PIVOT the results

Extracting values out of ntext

1 Answers1