Solved

TSQL XML question

Posted on 2014-03-02
6
294 Views
Last Modified: 2014-03-18
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

0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39898807
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
 

Author Comment

by:soozh
ID: 39898959
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
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 39898984
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39899026
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
 

Author Comment

by:soozh
ID: 39899054
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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39899081
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

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question