SQL Server - Errors while using an implicit SQL variable inside XML.value()

Question

Below codes are working and it outputs the first name:

DECLARE @xmlData XML = '<CustomerExtract><Delete User="Customer Deleter" ReasonCode="2"><Customer><CustomerID>9</CustomerID><Name><Title></Title><First>MOCHA</First><MI></MI><Last>MOCHA</Last><Suffix></Suffix></Name></Customer></Delete></CustomerExtract>'
DECLARE @xmlPath VARCHAR(MAX) = '(/CustomerExtract/Delete/Customer/'
select @xmlData.value('(/CustomerExtract/Delete/Customer/Name/First)[1]','VARCHAR(8000)')

Now, I need to use a dynamic variable so the XML path can change depending on the situation, but the outer elements doesn't change. Codes are something like this:

-- Check the CustomerUpdate type (Create / Update / Delete)
IF (@dataXML.exist('(/CustomerExtract/New)') = 1)
    SET @xmlPath = '/CustomerExtract/New/Customer/'
ELSE IF (@dataXML.exist('(/CustomerExtract/Change)') = 1)
    SET @xmlPath = '/CustomerExtract/Change/After/Customer/'
ELSE IF (@dataXML.exist('(/CustomerExtract/Delete)') = 1)
    SET @xmlPath = '/CustomerExtract/Delete/Customer/'
ELSE --Not supported!
    RETURN

However, when I try to use an SQL implicit variable like mentioned here: The argument 1 of the XML data type method "value" must be a string literal , I got some weird errors:

Sample 1: Starting parenthesis inside variable @xmlPath

DECLARE @xmlPath VARCHAR(MAX) = '(/CustomerExtract/Delete/Customer/'
select @xmlData.value('sql:variable("@xmlPath")Name/First)[1]','VARCHAR(8000)')

It throws this exception:

XQuery [value()]: No more tokens expected at the end of the XQuery expression. Found 'Name'.

Sample 2: Starting parenthesis NOT on variable

DECLARE @xmlPath VARCHAR(MAX) = '/CustomerExtract/Delete/Customer/'
select @xmlData.value('(sql:variable("@xmlPath")Name/First)[1]','VARCHAR(8000)')

It throws this exception:

XQuery [value()]: ")" was expected.

Sample 3:

DECLARE @xmlPath VARCHAR(MAX) = '/CustomerExtract/Delete/Customer'
select @xmlData.value('(sql:variable("@xmlPath")/Name/First)[1]','VARCHAR(8000)')

It throws this exception:

XQuery [value()]: A node or set of nodes is required for /

Can someone please explain me why does this happen and what is the correct syntax to achieve my desired results? Thanks

Gottfried Lesigang · Accepted Answer · 2018-08-15 10:09:26Z

2

The XPath within .value() must be a literal. You cannot inject variable parts here, but there are several options:

DECLARE @xmlData XML = '<CustomerExtract><Delete User="Customer Deleter" ReasonCode="2"><Customer><CustomerID>9</CustomerID><Name><Title></Title><First>MOCHA</First><MI></MI><Last>MOCHA</Last><Suffix></Suffix></Name></Customer></Delete></CustomerExtract>'
DECLARE @xmlPath VARCHAR(MAX) = '(/CustomerExtract/Delete/Customer/'

--You can declare the whole thing dynamically ...

DECLARE @cmd NVARCHAR(MAX)=
N'select @xmlData.value(''' +  @xmlPath + '/Name/First)[1]'',''VARCHAR(8000)'')';
--... and use sp_executesql to run this code
EXEC sp_executesql @cmd,N'@XmlData XML',@xmlData=@xmlData;

--you can omit the name with a wildcard and a deep search (//) after

select @xmlData.value('(/CustomerExtract/*//Customer/Name/First)[1]','VARCHAR(8000)')

--you can use a nodeName variable inside a predicate to test against local-name()
--Due to the <After> at <Change> you'd need a deep search(//) to find the <Customer> in any case

DECLARE @nodeName NVARCHAR(100)=N'Delete';
select @xmlData.value('(/CustomerExtract/*[local-name()=sql:variable("@nodeName")]//Customer/Name/First)[1]','VARCHAR(8000)')

answered Aug 15, 2018 at 10:09

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cyberpau Over a year ago

Thanks! It's exactly what I'm looking for (but somehow disappointed that I cannot put / inside the @xmlPath variable - makes life easier and SQL script shorter). I found similar answer here but your post is much easier to understand for me. Thank you so much! Just to add - dynamic sql are prone to SQL injections The Curse and Blessings of Dynamic SQL by Erland Sommarskog

Gottfried Lesigang Over a year ago

@Cyberpau, about sql injection: This is relevant if there is any unvalidated user input. In the case given I'd assume, that this is safe as the code is created by your own logic without any external input. And just to mention: You might tink about using all your statements together within COALESCE...

Collectives™ on Stack Overflow

SQL Server - Errors while using an implicit SQL variable inside XML.value()

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related