OPENXML issue. Help needed... - 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 2005 Forums

Transact-SQL (2005)

OPENXML issue. Help needed...

Author

Topic

ratinakage
Starting Member

16 Posts

Posted - 2007-05-04 : 06:12:17

Hey guys,

If you could run the following SQL command:

I am trying to extract two Addendum elements from the xml string.

DECLARE @string VARCHAR (200)

SET @string = '<Request><Addendum>Addendum1</Addendum><Addendum>Addendum2</Addendum></Request>'

DECLARE @xml_xml_ptr INT

EXEC sp_xml_preparedocument @xml_xml_ptr OUTPUT, @string

SELECT
Addendum
FROM
OPENXML (@xml_xml_ptr, '/Request')
WITH
(
Addendum VARCHAR(79) 'Addendum'
)

EXEC sp_xml_removedocument @xml_xml_ptr

The problem is, it only returns the first addendum and not the second..

Any help would be much appreciated.

Thanks!
G

ratinakage
Starting Member

16 Posts

Posted - 2007-05-04 : 07:24:02

Found the answer!! Thanks to this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60662

Correct SQL below...

DECLARE @string VARCHAR (150)

SET @string = '<Request><Addendum>Addendum1</Addendum><Addendum>Addendum2</Addendum></Request>'

DECLARE @xml_xml_ptr INT

EXEC sp_xml_preparedocument @xml_xml_ptr OUTPUT, @string

SELECT
Addendum
FROM
OPENXML (@xml_xml_ptr, '/Request/Addendum')
WITH
(
Addendum VARCHAR(79) '.'
)

EXEC sp_xml_removedocument @xml_xml_ptr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources