TSQL XML question

I have an document where i need to look to see if an element exists and has data.  

So i use @xml.exist and @xml.value to first check it exists and then if it has a value.

I need to do this with 50 or so elements so i would like to write a function that does both tests.  This will make my code a bit easier to read.

So i have tried but there is something wrong with it as it always says there is a value.  Maybe it is not possible to do this.  Here is my function.  Can anyone see what is wrong with it?

alter FUNCTION pharos_Exists
(
	-- Add the parameters for the function here
	@xml xml,
	@variable nvarchar(100) 
)
RETURNS int 
AS
BEGIN
	-- Declare the return variable here
  declare @exists int ;
  --'(SweAAABas2012/U1SuprarenalDiameter)[1]'
  
  set @exists = 1 ;

  if (@xml.exist( 'sql:variable("@variable")' ) = 0) or (@xml.value( 'sql:variable("@variable")', 'nvarchar(100)') = '')
    set @exists = 0 ;
	
	-- Return the result of the function
  return @exists 

END
GO

Open in new window

soozhCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert SchuttSoftware EngineerCommented:
You seem to be checking variable itself, not the corresponding value in the xml document.

If the commented out "SweAAABas2012/U1SuprarenalDiameter" is an example of what you want to search for, then I don't have a real easy solution, but keep reading ;-)

If you just need 1 node then you can use this:
alter FUNCTION pharos_Exists
(
	@xml xml,
	@variable nvarchar(100)
)
RETURNS int 
AS
BEGIN
  declare @exists int ;
  set @exists = 1 ;
  
  if (@xml.exist( '//*[local-name()=sql:variable("@variable")]' ) = 0)
  or (@xml.value( '(//*[local-name()=sql:variable("@variable")])[1]', 'nvarchar(100)') = '')
    set @exists = 0 ;
	
  return @exists 

END
GO

Open in new window

If you do need to check for a path like "SweAAABas2012/U1SuprarenalDiameter" then you could use this: (call it with the 2 node names separately)
alter FUNCTION pharos_Exists
(
	@xml xml,
	@variable nvarchar(100),
	@variable2 nvarchar(100)
)
RETURNS int 
AS
BEGIN
  declare @exists int ;
  set @exists = 1 ;
  
  if (@xml.exist( '//*[local-name()=sql:variable("@variable")]/*[local-name()=sql:variable("@variable2")]' ) = 0)
  or (@xml.value( '(//*[local-name()=sql:variable("@variable")]/*[local-name()=sql:variable("@variable2")])[1]', 'nvarchar(100)') = '')
    set @exists = 0 ;
	
  return @exists 

END
GO

Open in new window

0
soozhCEOAuthor Commented:
Thanks.

You are correct that i am looking for something like "SweAAABas2012/U1SuprarenalDiameter".  

I am Always looking for something in the format "SweAAABas2012/ xxxx "

So maybe i can hard code that in my function instead of @variable?

Can you provide the syntax?  i really dont understand xquery!
0
Robert SchuttSoftware EngineerCommented:
Yeah sure:
alter FUNCTION pharos_Exists
(
	@xml xml,
	@variable nvarchar(100)
)
RETURNS int 
AS
BEGIN
  declare @exists int ;
  set @exists = 1 ;
  
  if (@xml.exist( '//SweAAABas2012/*[local-name()=sql:variable("@variable")]' ) = 0)
  or (@xml.value( '(//SweAAABas2012/*[local-name()=sql:variable("@variable")])[1]', 'nvarchar(100)') = '')
    set @exists = 0 ;
	
  return @exists 

END
GO

Open in new window

If you have an exact location in the document then you can use that for example /root/data/record/SweAAABas2012 instead of //SweAAABas2012 which searches for that node on any level in the document.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Robert SchuttSoftware EngineerCommented:
By the way, if you're gonna call this function 50 times then there may be a more efficient way of doing this (using .nodes if I remember correctly), but I'd have to look into that and know more about what you need to do exactly.
0
soozhCEOAuthor Commented:
i would be interested in the nodes solution.

What i am doing is validating some xml and i need to check whether a node exits and if it does if it has a value..
0
Robert SchuttSoftware EngineerCommented:
There's some Examples here: http://technet.microsoft.com/en-us/library/ms188282.aspx

A start could be:
DECLARE @x xml 
SET @x='<root><some><more><SweAAABas2012><U1SuprarenalDiameter>10mm</U1SuprarenalDiameter><U1SuprarenalDiameter2></U1SuprarenalDiameter2></SweAAABas2012></more></some></root>'

SELECT CASE WHEN ISNULL(T.c.value('U1SuprarenalDiameter[1]', 'nvarchar(100)'), '') = '' THEN 0 ELSE 1 END AS U1SuprarenalDiameter -- 1 because there is data
	, CASE WHEN ISNULL(T.c.value('U1SuprarenalDiameter2[1]', 'nvarchar(100)'), '') = '' THEN 0 ELSE 1 END AS U1SuprarenalDiameter2 -- 0 because it's empty
	, CASE WHEN ISNULL(T.c.value('U1SuprarenalDiameter3[1]', 'nvarchar(100)'), '') = '' THEN 0 ELSE 1 END AS U1SuprarenalDiameter3 -- 0 because it's missing
FROM   @x.nodes('//SweAAABas2012') T(c)

Open in new window

But if you can tell me how you need the data presented, then maybe this can be shortened, although that might actually hurt performance...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.