Solved

TSQL XML question

Posted on 2014-03-02
6
284 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now