problem with 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

SQL Server Development (2000)

problem with OPENXML()

Author

Topic

rajikrishna
Starting Member

12 Posts

Posted - 2006-01-21 : 06:55:22

Hi All,

I am trying to parse a XML string using OpenXML but my XML is not getting read.

Please tell me whats the error in the below code.

DECLARE @XMLSTRING NVARCHAR(MAX)
DECLARE @DOCHANDLE INT

SET @XMLSTRING = '<AcknowldegeStatusChangeRequest><StatusId>1</StatusId><StatusId>16</StatusId></AcknowldegeStatusChangeRequest>'

DECLARE @tempStatus TABLE
(
statusID NVARCHAR(30)
)

EXEC sp_xml_preparedocument @DOCHANDLE OUTPUT, @XMLSTRING

INSERT INTO @tempStatus
SELECT StatusId FROM
OPENXML(@DOCHANDLE,'AcknowldegeStatusChangeRequest/StatusId',3)
WITH (StatusId NVARCHAR(30))

SELECT * FROM @tempStatus

EXEC sp_xml_removedocument @DOCHANDLE

I am not getting any records in the @tempStatus table.

help needed!!

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 06:59:53

is your path too deep?

INSERT INTO @tempStatus
SELECT StatusId FROM
OPENXML(@DOCHANDLE,'AcknowldegeStatusChangeRequest~~/StatusId~~',3)
WITH (StatusId NVARCHAR(30))

Kristen

rajikrishna
Starting Member

12 Posts

Posted - 2006-01-21 : 07:06:07

Yeah i tried what u said.

Its reading only the first node , its not reading all the nodes.......

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 09:46:28

Yeah, looking at it more closely your XML is wrong. You neeed something like


<AcknowldegeStatusChangeRequest>
	<Status>
		<StatusId>1</StatusId>
	</Status>
	<Status>
		<StatusId>2</StatusId>
	</Status>
</AcknowldegeStatusChangeRequest>

and then

OPENXML(@DOCHANDLE,'AcknowldegeStatusChangeRequest/Status',3)

Kristen

jbkayne
Posting Yak Master

100 Posts

Posted - 2006-01-21 : 15:35:36

Try the following:

SELECT StatusId FROM
OPENXML(@DOCHANDLE,'/AcknowldegeStatusChangeRequest/StatusId',3)
WITH (StatusId NVARCHAR(30) '.')

rajikrishna
Starting Member

12 Posts

Posted - 2006-01-22 : 22:23:01

Hi,

In reference to Kristen's post
why is my XML wrong, the nodes are opened and closed properly.
There should be a valid reason for ur statement hence i would like to know.

I already tried what jbkayne said but i am not getting it.
I cannot change my XML format but if its wrong i need to justify why its wrong.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-22 : 23:16:22

The XML format that Kristen suggested seems to work.


declare @dochandle int
declare @xml nvarchar(4000)

set @xml =
'
<AcknowldegeStatusChangeRequest>
	<Status>
		<StatusId>1</StatusId>
	</Status>
	<Status>
		<StatusId>2</StatusId>
	</Status>
	<Status>
		<StatusId>3</StatusId>
	</Status>
</AcknowldegeStatusChangeRequest>
'

exec sp_xml_preparedocument @dochandle output, @XML 

print 'XML output'
select
	StatusId
from 
	openxml(@dochandle,'AcknowldegeStatusChangeRequest/Status',3)
with 
	(StatusId nvarchar(30))

exec sp_xml_removedocument @dochandle 


Results:
XML output
StatusId                       
------------------------------ 
1
2
3

(3 row(s) affected)

CODO ERGO SUM

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-22 : 23:38:19

If you dont want to change the XML structure then go with what jbkayne has suggested . It will give you the desired output

DECLARE @XMLSTRING NVARCHAR(500)
DECLARE @DOCHANDLE INT

SET @XMLSTRING = '<AcknowldegeStatusChangeRequest>
<StatusId>1</StatusId>
<StatusId>16</StatusId>
<StatusId>17</StatusId>
<StatusId>18</StatusId>
</AcknowldegeStatusChangeRequest>'
DECLARE @tempStatus table
(
statusID NVARCHAR(30)
)

EXEC sp_xml_preparedocument @DOCHANDLE OUTPUT, @XMLSTRING

INSERT INTO @tempStatus
SELECT StatusId FROM
OPENXML(@DOCHANDLE,'AcknowldegeStatusChangeRequest/StatusId',3)
WITH (StatusId NVARCHAR(30)'.')

SELECT * FROM @tempStatus

EXEC sp_xml_removedocument @DOCHANDLE

StatusId
------------------------------
1
16
17
18

rajikrishna
Starting Member

12 Posts

Posted - 2006-01-23 : 00:20:11

" WITH (StatusId NVARCHAR(30) '.') ".......

The "." worked.
Thankx for your help

Kristen
Test

22859 Posts

Posted - 2006-01-23 : 01:56:03

"In reference to Kristen's post why is my XML wrong"

Well, "wrong" is probably the wrong term, and my XML knowledge is limited, but the way I look at it:

You have an <AcknowldegeStatusChangeRequest> record which MIGHT contain values of its own [but doesn't just now], and then there are child values <StatusId> -which in my mind are more easily presented as a subset of <Status> (rather than as a subset of values under <AcknowldegeStatusChangeRequest>

I would also reckon that you needed a ROOT token. Currently <AcknowldegeStatusChangeRequest> is doing that, but you won't be able to add another one IF you did want to have multiple <AcknowldegeStatusChangeRequest> rows.

But that's just me!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources