asked on

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

Robert Schutt

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

soozh

ASKER

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!

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Schutt

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.

soozh

ASKER

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

Robert Schutt

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