FouadDaniels
asked on
How do I parse an xml string send to a SP
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
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
ASKER
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></R oot>'
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 ?
DECLARE @hr int
EXEC @hr = sp_OACreate 'MSXML2.DOMDocument', @object OUT
EXEC @hr = sp_OAMethod @object, 'LoadXML', '<Root><Node></Node><Node>
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
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
>>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
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
ASKER
But how do I traverse the childNodes.
SPEUDO CODE:
FOR EACH NODE IN @intDocRef.documentElement .childNode s
do something with the node
Next NODE
SPEUDO CODE:
FOR EACH NODE IN @intDocRef.documentElement
do something with the node
Next NODE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Typo in the post above (guess you had noticed)
To test , use
SELECT dbo.AnotherFunction('<EXAM PLE><CUSTO MER id="1" type="B">Jones</CUSTOMER>< CUSTOMER id="2" type="C">Johnson</CUSTOMER ></EXAMPLE >', 'CUSTOMER')
To test , use
SELECT dbo.AnotherFunction('<EXAM
What exactly are you trying to achieve? Have you looked at the OpenXML function?
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
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
ASKER
I am not at work now. tomorow all come back on this. sorry for the delay.
Regards,
Fouad Daniels
Regards,
Fouad Daniels
ASKER