Solved

How do I parse an xml string send to a SP

Posted on 2004-03-25
9
518 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
0
Comment
Question by:FouadDaniels
[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
  • 4
9 Comments
 

Author Comment

by:FouadDaniels
ID: 10675749
The server is remotely hosted and the SP i make might not have all the security rights.
0
 

Author Comment

by:FouadDaniels
ID: 10675776
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?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10675806
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
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:FouadDaniels
ID: 10675850
But how do I traverse the childNodes.

SPEUDO CODE:

FOR EACH NODE IN @intDocRef.documentElement.childNodes
    do something with the node
Next NODE
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 10676481
This time the code's mine
It uses another method to loop through the nodes

CREATE FUNCTION [dbo].[AnotherFunction] (@Xml TEXT, @tag VARCHAR(50))  
RETURNS VARCHAR(8000)
BEGIN

     DECLARE @intNodeCount      INTEGER
     DECLARE @intDocRef      INTEGER
     DECLARE @intNodeList      INTEGER
     DECLARE @intNode      INTEGER

     DECLARE @intOut           INTEGER
     DECLARE @Return      INTEGER
     DECLARE @SelectResult      VARCHAR(8000)
     DECLARE @NodeText varchar(1000)
     declare @i int

     set @SelectResult = ''

     EXECUTE @intOut = sp_OACreate 'MSXML2.DOMDocument', @intDocRef OUT
     -- load file
     EXECUTE @intOut = sp_OAMethod @intDocRef, 'LoadXML', @Return OUT, @Xml
     -- loop through nodes of a given type (@tag parameter)
     EXECUTE @intOut = sp_OAMethod @intDocRef, 'getElementsByTagName', @intNodeList OUT, @tag
     -- get nodecount
     EXECUTE @intOut = sp_OAGetProperty @intNodeList, 'length', @intNodeCount OUT
     set @i = 0
     while @i < @intNodeCount
     begin
          -- get a pointer to each node
          EXECUTE @intOut = sp_OAMethod @intNodeList, 'nextNode', @intNode OUT
          -- get node properties
          EXECUTE @intOut = sp_OAGetProperty @intNode, 'Text', @nodetext OUT
          select @SelectResult = @SelectResult + '-' + @nodetext, @i = @i + 1
     end
     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>', 'CUSTOMER')


This sample code could be used for any other purpose like loading a table,
I think you get the idea how to loop through nodes and get their properties

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10677059
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')
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10677628
What exactly are you trying to achieve?  Have you looked at the OpenXML function?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10704895
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
0
 

Author Comment

by:FouadDaniels
ID: 10704969
I am not at work now. tomorow all come back on this. sorry for the delay.

Regards,
Fouad Daniels
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 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