StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUDF which auto-switches between text and base64 xml attributes
text
Body
copied!<p>My application serializes data into various XML attributes, and depending on data it might send it as text, or as base64. In the latter case, attribute name will be "attribute-base64". So, on SQL server side it is possible to use following convention to decode XML:</p> <pre><code>declare @DataXml xml set @DataXml='<root v="test data"/>' ; --or: set @DataXml='<root v-base64="AgB0AGUAcwB0ACAAHwQSBCQEFw...."/>' ; SELECT ISNULL( @DataXml.value('root[1]/@v', 'nvarchar(max)'), CAST( @DataXml.value('xs:base64Binary(root[1]/@v-base64)', 'varbinary(max)') AS nvarchar(max) ) ) </code></pre> <p>I'm trying to write a UDF, which will accept XML and attribute name as an input and will output a string. How do I generate XQuery? I tried doing this, but it doesn't work:</p> <pre><code>CREATE FUNCTION dbo. udf_DataXmlValue ( @DataXml xml, @NodeName nvarchar(max), @AttributeName nvarchar(max) ) RETURNS nvarchar(max) AS BEGIN DECLARE @result nvarchar(max) ; DECLARE @xquery nvarchar(max) ; SET @xquery = @NodeName + '[1]/' + @AttributeName; SET @result = @DataXml.value('sql:variable("@xquery")', 'nvarchar(max)') ; IF @result IS NULL BEGIN ... do base64 stuff ... END RETURN @result ; END GO </code></pre> <p>When called :</p> <pre><code>SELECT dbo.udf_DataXmlValue( @xml, 'root', 'v' ) </code></pre> <p>return result is not value, but 'root[1]/@v'... Apparently, SQL server understands sql:variable("@xquery") as an XML value, not as XQuery. Any ideas what do I need to do?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload