Hello,
I have been looking for a C# example to call a SQL Stored Procedure and
parse an XML doc (foo.xml). The Stored Procedure should have 3 ins and one
out :
IN Example:
1- blobtext ntext
2- xmltagreader nvarchar(255)
3- defaultValue nvarchar(255)
OUT Example: Conditional out based would be nice to have -
If XML tag found then return the value between the XML tags: (striped the
tags out < & /> and grab all Values in between.
Else
Return Default Value
Ultimately I will call one SQL Procedure to call the above SQL Procedure to
process the foo.xml doc and have the value placed in a SQL DB for reporting.
This SQL XML Tag Parser will process the XML text and display the results in
the webform.aspx datatable or listbox.
Has anyone run into this kind of situation before? Any help would be greatly
appreciated.
Thanks in advance,
BillB
Stored Proc work in progress:
-------------------------------------------------------------------------
USE SPTagParser
GO
CREATE TABLE #SPTagParser
(Exception ntext,
Message ntext,
ChainedException ntext)
GO
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc output,
--INSERT OR READ FROM XML FILE...........
'
foo.xml doc text HERE
'
-- READ THE WHOLE DOC OR SPECIFY WHICH XML TAGS TO RETURN
INSERT #SPTagParser (Exception, Message, ChainedException)
SELECT Exception, id,ChainedException
FROM OPENXML(@hdoc, '/LoggedMessage/ChainedException', 5)
WITH (Exception ntext '../@id',
id ntext '@id',
ChainedException ntext '@ChainedException')
EXEC sp_xml_removedocument @hDoc
GO
SELECT * FROM #SPTagParser
GO
-- SEND XML TEXT TO webform.aspx and DISPLAY IN DATATABLE
-- AND WRITE TO SQL DB
DROP TABLE #SPTagParser
--------------------------------------------------------------------------