Extracting multiple values from a table column previously casted to XML

Question

I have a table column as nvarchar(max)containing the following XML data:

<?xml version="1.0" encoding="utf-8"?>
<SerializableAlertDetail xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="SerializableContextAlertDetail">
  <ContextName>Evénements PTI mobile</ContextName>
  <EnumValueName>Pré Alerte immobilisme</EnumValueName>
</SerializableAlertDetail>

I would like to SELECT the concatenation of the 'ContextName' and 'EnumValueName' XML elements.

At first I simply tried to return one element, which works fine:

SELECT CAST(REPLACE(dbo.AlertDetail.Context, 'encoding="utf-8"', '') AS XML).value('(/SerializableAlertDetail/*[local-name() = "ContextName"])[1]', 'nvarchar(max)') As DisplayName FROM Table

Because I do not want to cast twice in the query, I'm looking for a way to destructure the XML column into a table and select columns from here. So far I'm stuck with the following invalid query:

SELECT T0.XML.value('ContextName', 'nvarchar(max)')
FROM Table c
    CROSS APPLY (SELECT CAST(REPLACE(c.Context, 'encoding="utf-8"', '') AS XML)) as T(X)
    CROSS APPLY T.X.nodes('SerializableAlertDetail') AS T0(XML)

But it fails with the following error message:

XQuery [T.X.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Any help appreciated.

EDIT 1

I've come to the following query that works but may probably not be optimal:

SELECT T0.XML.query('./ContextName').value('.', 'nvarchar(max)') + T0.XML.query('./EnumValueName').value('.', 'nvarchar(max)')
FROM Table c
        CROSS APPLY (SELECT CAST(REPLACE(c.Context, 'encoding="utf-8"', '') AS XML)) as T(X)
        CROSS APPLY T.X.nodes('SerializableAlertDetail') AS T0(XML)

EDIT 2

Replaced ntext by nvarchar(max) ;)

The error is telling you the problem here. Try `T0.XML.value('ContextName[1]', 'nvarchar(max)')`. Also, you should really be storing your data as an `xml` if it's XML, and you really shouldn't be using `(n)text`; it's been deprecated since SQL Server 2005. If you need values longer than 4000/8000 characters use `(n)varchar(MAX)`. — Thom A, Feb 08 '19 at 09:17
You've understood that I'm working with an old database ;) Your suggestion works. You can Add an answer for me to accept. — , Feb 08 '19 at 09:30
The fact that you're casting your XML to `xml` and then the return type of the singleton is `nvarchar(MAX)`, does infer you have access to those datatypes though :) (hence my comment). — Thom A, Feb 08 '19 at 09:32

Shnugo · Accepted Answer · 2019-02-08T10:49:47.620

You were told already, that NTEXT is deprecated for centuries ;-)

Just some background:

NTEXT is 2-byte encoded text and will translate to NVARCHAR. But your XML is yelling I am UTF-8!!!!, which is a plain lie. Within NTEXT it is - for sure! - not UTF-8. There are two approaches to proceed:

Cast your NTEXT to NVARCHAR, then to VARCHAR and finally to XML. This will work, as UTF-8 is 1-byte encoded within plain latin.
Take away the lie by replacing utf-8 with 1) nothing (as you do), with 2) utf-16 or with 3) ucs-2 (the correct thing).

About your query: This can be done a bit simpler, as there is no repeating content, therefore there is no need for a derived table. Try this:

SELECT X.value('(/SerializableAlertDetail/ContextName/text())[1]','nvarchar(max)') 
     + X.value('(/SerializableAlertDetail/EnumValueName/text())[1]','nvarchar(max)')
FROM Table c
        CROSS APPLY (SELECT CAST(REPLACE(c.Context, 'encoding="utf-8"', '') AS XML)) as T(X);

This should be faster...

In terms of readability you might even try

SELECT X.value('(//ContextName)[1]','nvarchar(max)') 
     + X.value('(//EnumValueName)[1]','nvarchar(max)')
FROM Table c
        CROSS APPLY (SELECT CAST(REPLACE(c.Context, 'encoding="utf-8"', '') AS XML)) as T(X);

Indeed, I made a mistake pretending that the column type containing the XML was ntext. it is nvarchar(max). Simplified and readable version works like a charm. Thank you so much. — , Feb 08 '19 at 10:42

Extracting multiple values from a table column previously casted to XML

1 Answers1