The problem

Stored in a varchar column, payload_xml, we have xml that looks something like this:
<?xml version="1.0" encoding="utf-16"?>  
  xmlns:xsi="" xmlns:xsd="">    
I need to return the underlined section as a varchar.

The solution

With the help of Tim Chapman's article Shred XML data with XQuery, I've arrived at the following code:
select cast(cast(cast(payload_xml as nvarchar(max)) as xml)
as varchar(max)) as PaymentArrangementExternalReference,
Which is, admittedly a bit of a mess, but each part has its purpose, and it works!


Interesting observations from inside out:
  • cast as nvarchar so that the encoding matches "utf-16"
  • otherwise sql server fails with the cryptic 'XML parsing: ... unable to switch the encoding'
  • data() function returns the actual content of the node, not the whole node
  • I didn't need to use [] indexers because there is only one element.
Lesson learned: use the xml datatype for the column.


To handle different namespaces, e.g.

  <?xml version="1.0" encoding="utf-16"?>

add a namespace declaration to the query as follows:

select @x.query(
  'declare namespace ap="";