OPENXML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

OPENXML

Author

Topic

rkiss
Starting Member

7 Posts

Posted - 2004-11-10 : 08:36:25

Hi,

I have the following task.

I need to retrieve data from a database column. data is in xml form (no schema definition).

Here is a sample data:


<ParameterValues>
<ParameterValue>
<Name>TO</Name>
<Value>reportadmin@xxx.com</Value>
</ParameterValue>
<ParameterValue>
<Name>ReplyTo</Name>
<Value>reports@xxx.com</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeReport</Name>
<Value>True</Value>
</ParameterValues>

I would like to retrieve "TO" value (email address) and "ReplyTo" value (email address).

I know that I have to use OPENXML, but I just have no idea how the SELECT syntax would be done since I have never used OPENXML syntax and the examples that I found are not any good.

All your help is appreciated.

thanks

R

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-10 : 08:53:44

What is the datatype of your xml column?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 08:56:08

Actually, you don't need OPENXML:

declare @val varchar(1000)
select @val=substring(xmldata, CharIndex('<Name>ReplyTo</Name>', xmlData),
CharIndex('</ParameterValue>', xmldata,
CharIndex('<Name>ReplyTo</Name>', xmlData))-CharIndex('<Name>ReplyTo</Name>', xmlData))
from mytable

select substring(@val, charindex('<Value>', @val)+len('<Value>'), charindex('</Value>', @val)-charindex('<Value>', @val)-len('<Value>'))

I admit it's a little convoluted, but it works.

rkiss
Starting Member

7 Posts

Posted - 2004-11-10 : 09:00:27

Hi ehorn,

it is ntext - this is predifined by Microsoft - MS Reporting Services table - thanks

robvolk

i will give that a try - thanks

R

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-10 : 09:14:17

I thought this might be the case - SQL2000 does not provide a real elegant method to solving this problem. Yukon will fix this with varchar(max) but in the meantime have a look at the following article - especially Rob Boek's comments down towards the bottom.

http://www.sqlxml.org/faqs.aspx?faq=42

rkiss
Starting Member

7 Posts

Posted - 2004-11-10 : 09:15:24

robvolk - very cool. I was Oracle SQL and PL/SQL developer and recently switched to MS Server and TSQL (new job), so I still have a lot to learn. Thanks for the tip. I really appreciate it.

R

rkiss
Starting Member

7 Posts

Posted - 2004-11-10 : 09:15:59

ehorn - thanks for the link

R

rkiss
Starting Member

7 Posts

Posted - 2004-11-10 : 09:55:06

RobVolk,

how would this work if I want to retrieve mutliple columns.

The code below does not work

declare @to varchar(1000)
declare @replyto varchar(1000)
select @to=substring(ExtensionSettings, CharIndex('<Name>TO</Name>', ExtensionSettings),
CharIndex('</ParameterValue>', ExtensionSettings,
CharIndex('<Name>TO</Name>', ExtensionSettings))-CharIndex('<Name>TO</Name>', ExtensionSettings))
select @replyto=substring(ExtensionSettings, CharIndex('<Name>ReplyTo</Name>', ExtensionSettings),
CharIndex('</ParameterValue>', ExtensionSettings,
CharIndex('<Name>ReplyTo</Name>', ExtensionSettings))-CharIndex('<Name>ReplyTo</Name>', ExtensionSettings))
FROM dbo.Subscriptions

select substring(@to, charindex('<Value>', @to)+len('<Value>'), charindex('</Value>', @to)-charindex('<Value>', @to)-len('<Value>')) AS [TO_EMAIL],
substring(@replyto, charindex('<Value>', @replyto)+len('<Value>'), charindex('</Value>', @replyto)-charindex('<Value>', @replyto)-len('<Value>')) AS [REPLYTO_EMAIL]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 13:18:05

When you say "doesn't work", do you mean it returns nothing, or the wrong piece?

There will be a practical limit to what you can to do with this technique. I was pointing out that for simple extractions you could get by with Charindex() and Patindex(). If you need to parse all the XML then you'll almost certainly have to use OPENXML.

rkiss
Starting Member

7 Posts

Posted - 2004-11-10 : 13:25:10

you are right, i do need to use OPENXML, but the problem is that the xml code has no schema, thus it makes it very hard for me to parse it since I do not have any xml parsing experience. Do you know how the code above could be parsed?

Thanks

Roman

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 13:42:29

Yes, it's well-formed and is therefore parseable. Having a schema (XSD or DTD) is not necessary to parse it. The only problem I forsee is that you have chunks of XML on each row of your table, instead of having an entire table represented in XML. You'll have to write some kind of cursor or loop to process each chunk of XML per row, calling OPENXML each time.

rkiss
Starting Member

7 Posts

Posted - 2004-11-10 : 13:46:17

sounds good. I will try your suggestion.

thanks

roman

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-10 : 16:42:19

this should get you started roman:


set nocount on

--create some sample data
create table xmltable (id int identity(1,1),xmlcol ntext)
insert into xmltable
select 
'<ParameterValues>
	<ParameterValue>
		<Name>TO</Name>
		<Value>reportadmin@xxx.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>ReplyTo</Name>
		<Value>reports@xxx.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>IncludeReport</Name>
		<Value>True</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>TO</Name>
		<Value>reportadmin2@xxx.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>ReplyTo</Name>
		<Value>reports2@xxx.com</Value>
	</ParameterValue>
	<ParameterValue>
		<Name>IncludeReport</Name>
		<Value>True</Value>
	</ParameterValue>
</ParameterValues>'
go

--***************************************************************************
--Perform parsing of the text xml
--***************************************************************************

/*PART 1*/ 
DECLARE @id int 
DECLARE @idoc int 
SET @id = 1 -- or whatever the id 

DECLARE @datalen int 
DECLARE @sql varchar(8000) 
DECLARE @sql1 varchar(8000) 
DECLARE @sql2 varchar(8000) 
DECLARE @cnt int 
-- get the length 
SELECT @datalen = DATALENGTH (xmlcol) / 4000 + 1 FROM xmltable WHERE id = @id 

-- phase 1 collect into @sql declarations of @str1, @str2,...@strn 
SET @cnt = 1 
SET @sql='DECLARE ' 
SET @sql1 = '' 
WHILE (@cnt <= @datalen) 
BEGIN 
SELECT 
@sql = @sql + CASE @cnt 
WHEN 1 THEN '' 
ELSE ', ' + CHAR(13) 
END 
+ ' @str'+CONVERT(varchar(10),@cnt)+' NVARCHAR(4000)' 
SET @cnt = @cnt + 1 
END 
-- phase 2 collect into @sql selection of chunks (takng care of length) 
SET @cnt = 1 
WHILE (@cnt <= @datalen) 
BEGIN 
IF LEN(@sql) < 7850 
SELECT @sql = @sql + CHAR (13) + 
'SELECT @str' + CONVERT(VARCHAR(10), @cnt) + ' = REPLACE(SUBSTRING(xmlcol, ' + 
CONVERT(VARCHAR(30), (@cnt-1)*4000+1) + ', 4000),CHAR(39), CHAR(39)+CHAR(39) ) ' + 
'FROM xmltable ' + 
'WHERE id = ''' + CONVERT(VARCHAR(10),@id) + '''' 
ELSE 
SELECT @sql1 = @sql1 + CHAR (13) + 
'SELECT @str' + CONVERT(VARCHAR(10), @cnt) + ' = REPLACE(SUBSTRING(xmlcol, ' + 
CONVERT(VARCHAR(30), (@cnt-1)*4000+1) + ', 4000),CHAR(39), CHAR(39)+CHAR(39) ) ' + 
'FROM xmltable ' + 
'WHERE id = ''' + CONVERT(VARCHAR(10),@id) + '''' 
SET @cnt = @cnt + 1 
END 

/*PART 2*/ 
-- phase 3 preparing the 2nd level dynamic sql 
SELECT @sql1 = @sql1 + CHAR(13) + 'EXEC ('+ CHAR(13) + '''DECLARE @idoc int'+ CHAR(13) + 
'EXEC sp_xml_preparedocument @idoc OUT, '''''' + ' 
SET @cnt = 1 
WHILE (@cnt <= @datalen) 
BEGIN 
SELECT @sql1 = @sql1 + CHAR (13) + '@str' + CONVERT (varchar(10), @cnt) + ' +' 
SET @cnt = @cnt + 1 
END 
SET @sql1 = @sql1 + ' '''''' ' 
SET @sql1 = @sql1 + CHAR(13) + 'DECLARE idoc_cur CURSOR FOR SELECT @idoc''' +CHAR(13) + ')' 

/*
--debug code 
PRINT @sql 
--PRINT '@sql length=' +convert(varchar(5),datalength(@sql)) 
--PRINT '----------' 
PRINT @sql1 
--PRINT '@sql1 length=' +convert(varchar(5),datalength(@sql1)) 
PRINT @sql2 
--PRINT '----------' 
*/
 
EXEC (@sql + @sql1) 
OPEN idoc_cur 
FETCH NEXT FROM idoc_cur into @idoc 

-- At this point just use OPENXML to perform parsing on the @idoc handle
SELECT *
FROM OPENXML (@idoc, '/ParameterValues/ParameterValue',3)
WITH (
		Name   varchar(30),
      Value  varchar(30)
		)

DEALLOCATE idoc_cur 
exec sp_xml_removedocument @idoc 
go

drop table xmltable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources