Using xquery to select a node's inner xml as a string when it contains an html image tag

Question

I have an xml field that I need to select the inner xml from a node as a string regardless of what the inner xml is (in this case, it's an html image tag). I'm able to get plain text with no problems but when I have something with the image tag in it, I think it's treating it as a child node so it's not returning it.

declare @myXml xml = '<question><prompt><img src="someImage.png" /></prompt></question>';

select q.value('(prompt/text())[1]', 'nvarchar(max)') as prompt from @myXml.nodes('//question') qs(q);

The above is returning null. What I'm wanting is the actual contents of the node as a string.

Answer 1

This should work....

select q.query('(prompt/node())') as prompt from @myXml.nodes('//question') qs(q);

text() only returns text values from the child nodes, while node() returns the whole enchilada.

Add in a CAST to varchar, and you're done!

select cast(q.query('(prompt/node())') as varchar(100)) as prompt from @myXml.nodes('//question') qs(q);

Answer 2

XQuery treats XML as a tree of nodes. If you want to convert the tree of nodes to lexical XML markup, that operation is called 'serialization'. XQuery 3.0 provides a function fn:serialize() to convert nodes into lexical XML, but there's no equivalent in XQuery 1.0, except that some XQuery implementations may have an extension to do the job.