TSQL XML exists query with namespaces and IF-ELSE condition

Question

I want to write T-SQL code that combines the use of the XML exists() function with namespaces and an IF-ELSE construct.

From this thread I know that WITH XMLNAMESPACES is used to use namespaces. I also know that the exist() function is used to determine if a node is present in an XML variable.

Until now, I used exist() as follows:

IF ((@MyXML.exist('someNode')) = 0)
BEGIN
    -- Do Something
END
ELSE
BEGIN
    -- Do Something else
END

However I don't know how to combine this, if I need to use namespaces. I tried as follows, but get an error.

;WITH XMLNAMESPACES('http://www.sdmx.org/resources/sdmxml/schemas/v2_1/message' AS ns)
IF ((@MyXML.exist('ns:someNode')) = 0)
BEGIN
    -- Do Something
END
ELSE
BEGIN
    -- Do Something else
END

I get the following error:

Incorrect syntax near the keyword 'WITH'.

Hence, my question is: how can I use a T-SQL If-Else construct in combination with XML namespaces and the XML exist() function?

thanks that worked. And I learned that namespaces are case-sensitive. — beta
– beta, Commented Nov 26, 2018 at 10:46

Gottfried Lesigang · Accepted Answer · 2018-11-26 13:04:55Z

You've got one solution by Jeroen already (link to the docs). But - just for fun - I'd like to show you, that there are several ways to go:

DECLARE @xml XML=
N'<root xmlns:ns="dummy">
    <ns:test>1</ns:test>
  </root>';

--wildcard for the namespace

IF @xml.exist('//*:test[text()=1]')=1
    PRINT 'test 1-yes'
ELSE
    PRINT 'test 1-no';

--inline declaration (as told you in the docs)

IF @xml.exist('declare namespace ns="dummy";//ns:test[text()=1]')=1
    PRINT 'test 2-yes'
ELSE
    PRINT 'test 2-no';

--you can use all of the above to set a variable

DECLARE @check BIT = @xml.exist('//*:test[text()=1]');

--And - if needed - you still can use WITHXMLNAMESPACES like here to set a variable

WITH XMLNAMESPACES('dummy' AS ns)
SELECT @[email protected]('//ns:test[text()=1]');

IF @check=1
    PRINT 'test 4-yes'
ELSE
    PRINT 'test 4-no';

Altogether this smells a bit procedural... Might be, that there is a better approach entirely...

Collectives™ on Stack Overflow

TSQL XML exists query with namespaces and IF-ELSE condition

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related