SQL Query to Pars Out a Value from NTEXT (XML format) field

Question

I have XML data in an NTEXT field (ou.ORDMODE), that I need to parse out a value (description) from. Column may contain null values. The data looks like this:

<?xml version="1.0" encoding="utf-16"?>
<UDFValidatedValue xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Accellos.Platform.UDF">
  <Description>Export</Description>
  <Value>EXP</Value>
  <ValueType>String</ValueType>
</UDFValidatedValue>

The line I have in my query is this:

CAST(REPLACE(CAST(ou.ORDMODE as NVARCHAR(MAX)),' xmlns="http://schemas.datacontract.org/2004/07/Accellos.Platform.UDF"','') as XML).value ('(/UDFValidatedValue/Description/text())[0]', 'nvarchar(100)') as Mode3,

but Mode3 column is returned as blank.

What am I doing wrong?

Database platform tagged. Would appreciate some feedback on how to improve the question. — f2942, Apr 23 '17 at 22:12
It's because XML/XPath uses one-based indexing. e.g. Change `text())[0]` to `text())[1]`. And, actually, you shouldn't even really need the `text()` part, just `(/UDFValidatedValue/Description)[1]` would do. — ZLK, Apr 24 '17 at 00:13
Thanks for your reply. Made the suggested changes, but the field is still returned blank. — f2942, Apr 24 '17 at 01:42
@ZLK, You might [read this](http://stackoverflow.com/a/43242238/5089204)... Using `/text()` is not just syntax sugar... — Shnugo, Apr 24 '17 at 06:52

TriV · Accepted Answer · 2017-04-24T02:01:43.157

You could use this:

DEclare @xml xml = N'<?xml version="1.0" encoding="utf-16"?>
<UDFValidatedValue xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Accellos.Platform.UDF">
 <Description>Export</Description>
 <Value>EXP</Value>
 <ValueType>String</ValueType>
</UDFValidatedValue>';            

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Accellos.Platform.UDF')
select @xml.value ('(/UDFValidatedValue/Description)[1]', 'nvarchar(100)') as Mode3

or

select @xml.value ('declare namespace 
         udf="http://schemas.datacontract.org/2004/07/Accellos.Platform.UDF";
         (/udf:UDFValidatedValue/udf:Description)[1]', 'nvarchar(100)') as Mode3

Demo link: Rextester

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

As ZLK pointed out in his comment, your own approach seems to be quite okay, just the index must be 1 instead of 0. Try it here:

ad "quite okay": Actually this is absolutely far away from "quite okay", read my hint below...

DECLARE @tbl TABLE(SomeXmlInText TEXT);
INSERT INTO @tbl VALUES
('<?xml version="1.0" encoding="utf-16"?>
<UDFValidatedValue xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Accellos.Platform.UDF">
  <Description>Export</Description>
  <Value>EXP</Value>
  <ValueType>String</ValueType>
</UDFValidatedValue>');

SELECT SomeXmlInText
      ,CAST(REPLACE(CAST(SomeXmlInText as NVARCHAR(MAX)),' xmlns="http://schemas.datacontract.org/2004/07/Accellos.Platform.UDF"','') as XML).value ('(/UDFValidatedValue/Description/text())[1]', 'nvarchar(100)') as Mode3
FROM @tbl

From your answer I take, that there is still no result...

Some possible issues:

XML is not really as you expect it
more / other namespaces
some weird no, not for you issue :-D

Hint

As you probably know TEXT, NTEXT and IMAGE are deprecated for centuries and should really not be used any more... If you have the slightest chance to change this, then you should store your XML in the appropritate XML-type...

You should not need any casts or manipulations on string level. This can be working, but is really out-dated...

SQL Query to Pars Out a Value from NTEXT (XML format) field

2 Answers2

Hint