OpenXML returns only one node - 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 returns only one node

Author

Topic

RaviPillala
Starting Member

3 Posts

Posted - 2009-05-13 : 01:57:42

Hi There,

I'm trying to parse an xml using T-sql. I'm stuck when i'm trying to parse specific repeated node.

Bellow is the code i'm trying to execute which always returs inly one "ID" value 8. and ignoring other values.

can any one help to find out a way to get all the 3 id values.

----------------------------------------------------

DECLARE @MerchantCardInfo VARCHAR(8000),
@docHandle INT

SET @MerchantCardInfo='<CardInfo>
<ProcessingOptionNum>
<id>8</id>
<id>4</id>
<id>2</id>
</ProcessingOptionNum>
</CardInfo>'
--Merchant Card Info table data load form xml
EXEC sp_xml_preparedocument @docHandle OUTPUT,@MerchantCardInfo

SELECT *
FROM OPENXML(@docHandle, N'/CardInfo/ProcessingOptionNum',3)
WITH (id VARCHAR(20) './id')

----------------------------------------------------

Thanks
Ravi

--ravi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 02:05:05

DECLARE @MerchantCardInfo XML

set @MerchantCardInfo = ' ... '

SELECT g.value('.', 'INT) AS ID
FROM @MerchantCardInfo.nodes('/CardInfo/ProcessingOptionNum/id') AS f(g)

E 12°55'05.63"
N 56°04'39.26"

RaviPillala
Starting Member

3 Posts

Posted - 2009-05-13 : 11:38:26

Actually i'm trying to work with SQL 2000 not SQL 2005. IS there any way i can make it work with SQL 2000 also usine extended procedures

--ravi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 11:40:20

You could start by posting in the correct forum, a SQL Server 2000 forum.
This is a SQL Server 2005 forum.

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-05-13 : 11:41:42

moved to correct forum.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-13 : 13:36:26

what about this?


SELECT *
FROM OPENXML(@docHandle, N'/CardInfo/ProcessingOptionNum/id',2) 
WITH (id VARCHAR(20) '.')

RaviPillala
Starting Member

3 Posts

Posted - 2009-05-13 : 18:23:13

It works.... Thank you Visakh

--ravi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources