troubleshooting Question

XML Field and XPath

Avatar of allanau20
allanau20 asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
19 Comments2 Solutions599 ViewsLast Modified:
Hi Experts!

Thanks for reading this.

Have a function that query an xmldata and we need to know if in the xml it contains '//123ABC'.


How would you edit it to use xpath?

TIA!

Alter FUNCTION [dbo].some_funct_udf
(
	@Input VARCHAR(50) 
)  
RETURNS VARCHAR(1000)
AS
BEGIN

DECLARE @handle INT,          
            @xmlDoc as XML

SET @xmlDoc =  (  select xmldata          
      from SomeTable   
      where someID = @Input 
      FOR XML AUTO, ELEMENTS  )     
      
            EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc

            SELECT @xmlDoc= text FROM OPENXML (@handle,'/SomeTable/xmldata/', 3) where id = 3
            EXEC sp_xml_removedocument @handle
            EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc

            SELECT @xmlDoc = text FROM OPENXML (@handle,'', 3) where id = 13
            EXEC sp_xml_removedocument @handle
            SELECT @xmlDoc
            
            declare @Output int
            SET @Output = 0
            
            SELECT @Output = @xmlDoc.exist('//123ABC')
            
RETURN @Output
END
ASKER CERTIFIED SOLUTION
MlandaT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros