UDF which auto-switches between text and base64 xml attributes

Question

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:

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) ) )

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:

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

When called :

SELECT dbo.udf_DataXmlValue( @xml, 'root', 'v' )

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?

Answer 1

You can't. XML methods are just like SQL queries, they are statically compiled into execution plans and hence cannot accept dynamic parts. Where SQL cannot accept the FROM table to be a variable, XML cannot accept XPath and XQuery expressions to be variables.

You could resort to construct dynamic SQL, but you cannot do that in a function.