We help IT Professionals succeed at work.

How do I parse an xml string send to a SP

FouadDaniels
FouadDaniels asked
on
Medium Priority
570 Views
Last Modified: 2007-12-19
Hi Experts,

ASP receives an HTTP post with an XML string in it. ASP sends this string to a SP in SQL Server.

Now how do I instantia an XMLdocument COM object in SQL server and traverse the XML document.

Regards,

Fouad Daniels
Comment
Watch Question

Author

Commented:
The server is remotely hosted and the SP i make might not have all the security rights.

Author

Commented:
DECLARE @object int
DECLARE @hr int
EXEC @hr = sp_OACreate 'MSXML2.DOMDocument', @object OUT
EXEC @hr = sp_OAMethod @object, 'LoadXML', '<Root><Node></Node><Node></Node></Root>'

DECLARE @property varchar(255)
EXEC @hr = sp_OAGetProperty @object, 'nodeName', @property OUT
PRINT @property

EXEC @hr = sp_OADestroy @object

But how do I traverse the documentElement.childNodes?

Commented:
Hi FouadDaniels

>>how do I instantia an XMLdocument COM object in SQL server and traverse the XML document<<
The following code should help

I once copied it from another thread, and successfully used at my own site
I would have prefered to post a link to the initial question, but the search engine couldn't find it, and I haven't bookmarked it.

CREATE FUNCTION [dbo].[SomeFunction] (@Xml TEXT, @XPath VARCHAR(8000))  
RETURNS VARCHAR(8000)
BEGIN

     DECLARE @intNodeCount      INTEGER
     DECLARE @intDocRef      INTEGER
     DECLARE @intOut           INTEGER
     DECLARE @Return      INTEGER
     DECLARE @SelectResult      VARCHAR(8000)

     EXECUTE @intOut = sp_OACreate 'MSXML2.DOMDocument', @intDocRef OUT

     EXECUTE @intOut = sp_OAMethod @intDocRef, 'LoadXML', @Return OUT, @Xml
     EXECUTE @intOut = sp_OASetProperty @intDocRef, 'SelectionLanguage', 'XPath'
     EXECUTE @intOut = sp_OAMethod @intDocRef, 'selectSingleNode', @Return OUT, @XPath

     EXECUTE @intOut = sp_OAGetProperty @Return, 'Text', @SelectResult OUT
     EXECUTE @intOut = sp_OADestroy @intDocRef

     RETURN @SelectResult
END
go

SELECT dbo.SomeFunction('<EXAMPLE><CUSTOMER id="1" type="B">Jones</CUSTOMER><CUSTOMER id="2" type="C">Johnson</CUSTOMER></EXAMPLE>', '/EXAMPLE/CUSTOMER[. ="Jones"]')

Please be aware that I'm not the author of this code, so hopefully the one who deserves recognition for this code will read this post and participate

HTH

Hilaire

Author

Commented:
But how do I traverse the childNodes.

SPEUDO CODE:

FOR EACH NODE IN @intDocRef.documentElement.childNodes
    do something with the node
Next NODE
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Typo in the post above (guess you had noticed)
To test , use
SELECT dbo.AnotherFunction('<EXAMPLE><CUSTOMER id="1" type="B">Jones</CUSTOMER><CUSTOMER id="2" type="C">Johnson</CUSTOMER></EXAMPLE>', 'CUSTOMER')
CERTIFIED EXPERT
Top Expert 2012

Commented:
What exactly are you trying to achieve?  Have you looked at the OpenXML function?

Commented:
Hi FouadDaniels,
did my code help a bit ?

I didn't mention OPENXML because It won't be able to parse complicated XML trees, or simple XML trees with recursive structure ( eg when one <Element> tag can include one or more <Element> tags)

But, as Anthony suggested, it's definitely better than creating activeX objects if the file structure allows its usage.

Cheers

Hilaire

Author

Commented:
I am not at work now. tomorow all come back on this. sorry for the delay.

Regards,
Fouad Daniels
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.