XML Tag Parse / Reader Fun!

microsoft.public.sqlserver.xml

9 Dec 2004 4:07 AM

BillB

Hi all,

I searched 24 plus hours and came up with nothing for a stored procedure
that would read and strip an XML doc into text. This is a pretty good
representation of a stored proc to parse xml tags... Hope someone else can
benefit and use this code snipet.
Happy holidays to all!

Thanks,
BillB

-------------ENJOY---------------
CREATE PROCEDURE TagParse
AS
Declare @myObj int
Declare @ping varchar(8000)
Declare @source varchar(255)
Declare @description varchar(255)
Declare @hr int
Declare @output varchar(255)

-- Creating COM object
Exec sp_OACreate 'MSXML2.DOMDocument', @myObj OUT

-- Trapping error while creating COM object
EXEC sp_OAGetErrorInfo @myObj, @source OUT, @description OUT
Select @output = 'Source:' + IsNull(@source, 'Null Source')
Print @output
Select @output = 'Description:' + IsNull(@description, 'Null Description')
Print @output

-- Calling method of COM object and xml file location
Exec sp_OAMethod @myObj, 'load', @ping OUT, 'C:\yourXMLhere.xml'
Exec sp_OAGetProperty @myObj, 'xml', @ping OUT
Exec sp_OADestroy @myObj
--------------------------
DECLARE @idoc int

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @ping

-- Execute a SELECT statement that uses the OPENXML rowset provider and the
XML <root> node.
drop table #xml
SELECT    * Into #xml
FROM       OPENXML (@idoc, '/rootXMLDocTagHere',1)

-- Choose a return
-- Returns all XML data from file - XML TAG ='s LocalName - TEXT ='s Text
for DB update
Select P.LocalName, C.Text From #xml P Inner Join #xml C On P.id =
C.parentid
Where C.Text Is Not NULL

-- Returns only columns with data in them - (No column name)
Select P.LocalName + ', ' + substring(C.Text,1,1000) From #xml P inner Join
#xml C On P.id = C.parentid
Where C.Text Is Not NULL

-- Returns all values in a table
Select * From #xml

-----------END-------------

Using the bulk insert method..................

CREATE TABLE #jb(col1 ntext)
go
BULK INSERT #jb
   FROM 'C:\YourXMLFile.xml'
Select * from #jb

14 Dec 2004 5:25 PM

RobKaratzas

Hi Bill

1. Do a google search for 'regexObjReplace udf'. this is a nice set of UDFs
for doing regex pattern matching and replaces.

2. Stripping the tags from an XML isn't really that bad (take a look at the
regex samples 4guysfromrolla has for doing the same thing with HTML files.

3. The file reading in an sp I do via sp_OA.

would be glad to e-mail the UDFs and file reading sp samples
(tu***@ebiz-developer.com)

rob

Show quote

"BillB" wrote:

> Hi all,
>
> I searched 24 plus hours and came up with nothing for a stored procedure
> that would read and strip an XML doc into text. This is a pretty good
> representation of a stored proc to parse xml tags... Hope someone else can
> benefit and use this code snipet.
> Happy holidays to all!
>
> Thanks,
> BillB
>
> -------------ENJOY---------------
> CREATE PROCEDURE TagParse
> AS
> Declare @myObj int
> Declare @ping varchar(8000)
> Declare @source varchar(255)
> Declare @description varchar(255)
> Declare @hr int
> Declare @output varchar(255)
>
> -- Creating COM object
> Exec sp_OACreate 'MSXML2.DOMDocument', @myObj OUT
>
> -- Trapping error while creating COM object
> EXEC sp_OAGetErrorInfo @myObj, @source OUT, @description OUT
> Select @output = 'Source:' + IsNull(@source, 'Null Source')
> Print @output
> Select @output = 'Description:' + IsNull(@description, 'Null Description')
> Print @output
>
> -- Calling method of COM object and xml file location
> Exec sp_OAMethod @myObj, 'load', @ping OUT, 'C:\yourXMLhere.xml'
> Exec sp_OAGetProperty @myObj, 'xml', @ping OUT
> Exec sp_OADestroy @myObj
> --------------------------
> DECLARE @idoc int
>
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @idoc OUTPUT, @ping
>
> -- Execute a SELECT statement that uses the OPENXML rowset provider and the
> XML <root> node.
> drop table #xml
> SELECT    * Into #xml
> FROM       OPENXML (@idoc, '/rootXMLDocTagHere',1)
>
> -- Choose a return
> -- Returns all XML data from file - XML TAG ='s LocalName - TEXT ='s Text
> for DB update
> Select P.LocalName, C.Text From #xml P Inner Join #xml C On P.id =
> C.parentid
> Where C.Text Is Not NULL
>
> -- Returns only columns with data in them - (No column name)
> Select P.LocalName + ', ' + substring(C.Text,1,1000) From #xml P inner Join
> #xml C On P.id = C.parentid
> Where C.Text Is Not NULL
>
> -- Returns all values in a table
> Select * From #xml
>
> -----------END-------------
>
> Using the bulk insert method..................
>
> CREATE TABLE #jb(col1 ntext)
> go
> BULK INSERT #jb
>    FROM 'C:\YourXMLFile.xml'
> Select * from #jb
>
>
>
>
>