October 19, 2018 at 9:28 am
So this is part of my problem which I've requested assistance from on a separate topic. Didn't want to pollute that thread and instead ask independent question. The following is driving me nuts
DECLARE @x XML
SET @x = '<Data>
<e2>Whoop Dee Do</e2>
<e3>Whoa!</e3>
</Data>'
DECLARE @token VARCHAR(128)
SET @token = '/Data/e1'
SELECT @x.exist('sql:variable("@token")')
The result from above is 1. How??? XML element <one> does not exist in the XML. I've looked at documentation for exist() regarding how it returns 1 based on results, but I'm not able to explain the above. I need to detect presence of a node, I don't care about it's value, so I can either replace it's value or insert new node with value.
I'm sure I'm missing something very basic here, and hoping someone can set me straight quickly.
October 19, 2018 at 10:25 am
When a sql:variable() value is substituted in the query, it is treated as a literal string, wrapped in double-quotes, instead of as an XML node test.
Literal strings used by themselves without XML node semantics in XQuery falls into FLOWR-like behavior without using FLOWR commands, where the XQuery is constructing the XML output from parts it finds in the query: the return value is the result of putting those strings together and casting that to XML.
The .exist() method will return (1) because the literal string returned by the XQuery in the .exist() method is a result, even if it's not the result you want.
To see how the .exist method sees your variable, run some queries with direct values and compare them to the results using sql:variable().
DECLARE @x XML
SET @x = '<Root>
<two>Whoop Dee Do</two>
<three>Whoa!</three>
</Root>';
DECLARE @token varchar(128) = '/Data/one';
/* 1. literal node test: no results (desired effect) */
SELECT @x.query('/Data/one');
/* 2. literal string: XQuery FLOWR (returns the string) */
SELECT @x.query('"/Data/one"')
/* 3. using sql:variable (treated as literal string wrapped with double-quotes) */
SELECT @x.query('sql:variable("@token")')
/* 4. Because query #3 returns a value (even if it's not the one you want) the .exist() method will return (1) */
SELECT @x.exist('sql:variable("@token")')
It's sort of the XQuery version of doing this with T-SQL:/* This will fail, you can't use string variables to represent objects */
DECLARE @MyTbl varchar(32) = 'dbo.MyTable', @MyCol varchar(32) = 'Column1';
SELECT @MyCol FROM @MyTbl
-Eddie
Eddie Wuerch
MCM: SQL
October 19, 2018 at 10:30 am
Pagan DBA - Friday, October 19, 2018 9:28 AMSo this is part of my problem which I've requested assistance from on a separate topic. Didn't want to pollute that thread and instead ask independent question. The following is driving me nuts
DECLARE @x XML
SET @x = '<Root>
<two>Whoop Dee Do</two>
<three>Whoa!</three>
</Root>'DECLARE @token VARCHAR(128) = '/Data/one'
SELECT @x.exist('sql:variable("@token")')The result from above is 1. How??? XML element <one> does not exist in the XML. I've looked at documentation for exist() regarding how it returns 1 based on results, but I'm not able to explain the above. I need to detect presence of a node, I don't care about it's value, so I can either replace it's value or insert new node with value.
I'm sure I'm missing something very basic here, and hoping someone can set me straight quickly.
It's because 'sql:variable("@token")' returns a value, not a path. The value is treated as a degenerate node, so the exist function returns 1, because a (degenerate) node was returned. If you want to use dynamic paths, it looks like you'll need to use dynamic SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 19, 2018 at 11:14 am
Eddie Wuerch & drew.allen
Really appreciate the explanations you provided. Now I understand what I want is to do a COUNT() on Eddie's 1st example. That's what returns no rows = /Data/one is absent. However my problem is I do not know it will be /Data/one is advance. I need to be able to pass 'one' dynamically.
Are we saying there is no way other than to resort to Dynamic SQL with sp_executeSql for the entire query? I would have thought my problem wouldn't be unique and there would be a way. If you can please advise that way I don't try climbing up wrong tree.
October 19, 2018 at 11:47 am
Pagan DBA - Friday, October 19, 2018 11:14 AMEddie Wuerch & drew.allen
Really appreciate the explanations you provided. Now I understand what I want is to do a COUNT() on Eddie's 1st example. That's what returns no rows = /Data/one is absent. However my problem is I do not know it will be /Data/one is advance. I need to be able to pass 'one' dynamically.Are we saying there is no way other than to resort to Dynamic SQL with sp_executeSql for the entire query? I would have thought my problem wouldn't be unique and there would be a way. If you can please advise that way I don't try climbing up wrong tree.
DECLARE @x XML
SET @x = '<Root>
<two>Whoop Dee Do</two>
<three>Whoa!</three>
</Root>'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 19, 2018 at 6:01 pm
drew.allen - Friday, October 19, 2018 11:47 AMNot necessarily. The problem is that you were storing the entire path in the variable. If you only store the final node in the variable you can do it without being dynamic.
DECLARE @x XML
SET @x = '<Root>
<two>Whoop Dee Do</two>
<three>Whoa!</three>
</Root>'DECLARE @token VARCHAR(128) = 'one'
SELECT @x.exist('/Root/*[local-name() = sql:variable("@token")]')Drew
Doh! I used local-name() already when I created my "update" query on the XML. So sorry, that was stupid question.
I have it working now, thanks to you. Now I can go and fix my other problem
Much obliged.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply