Solved

TSQL XML question

Posted on 2014-03-02
6
286 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Suggested Solutions

Title # Comments Views Activity
SSRS  - Dropdown with Null 3 25
SSRS 2013 - Creating a summarized report 19 35
Text file into sql server 5 27
sql 2008 how to table join 2 16
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 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