XML - Error Retrieving Node Previous Level

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)

XML - Error Retrieving Node Previous Level

Author

Topic

sqlyukon
Starting Member

8 Posts

Posted - 2006-06-29 : 14:10:27

Please execute the below script- and help me get the desired results as suggested at the bottom.

Thanks.

CREATE PROCEDURE dbo.AppTransXMLInsert
(
@xml_text text
)
AS

SET NOCOUNT ON

BEGIN

DECLARE @i int

-- create internal representation of the document
EXEC sp_xml_preparedocument @i OUTPUT, @xml_text

BEGIN TRAN

SELECT
ApplicationID ,
TransactionID ,
BrowserID
FROM OPENXML(@i, '/MyApp/AppTrans',2)
WITH (
ApplicationID int,
TransactionID int,
BrowserID int '../@BrowserID'
)

IF @@ERROR <> 0
BEGIN
RAISERROR('Error occured while populating', 16, 1)
ROLLBACK
RETURN
END

COMMIT TRAN

-- cleanup:
EXEC sp_xml_removedocument @i

--SELECT * FROM ScriptHealth

-- return success
RETURN 0

END
GO

Exec dbo.AppTransXMLInsert
'
<MyApp>
<BrowserIndicator>2</BrowserIndicator>
<BrowserID>5</BrowserID>
<BrowserName>Order</BrowserName>
<BrowserVersion>1212</BrowserVersion>
<AppTrans>
<ApplicationID>1</ApplicationID>
<ApplicationName>Demo Application</ApplicationName>
<TransactionID>1</TransactionID>
<TransactionName>Transaction1</TransactionName>
</AppTrans>
<AppTrans>
<ApplicationID>1</ApplicationID>
<ApplicationName>Demo Application</ApplicationName>
<TransactionID>2</TransactionID>
<TransactionName>Transaction2</TransactionName>
</AppTrans>
</MyApp>
'

/*
Results -
-------
ApplicationID TransactionID BrowserID
1 1 NULL
1 2 NULL

Desired Results -
---------------
ApplicationID TransactionID BrowserID
1 1 5
1 2 5

mandm
Posting Yak Master

120 Posts

Posted - 2006-06-29 : 15:20:18

Change your select query to this.

SELECT
ApplicationID ,
TransactionID ,
BrowserID
FROM OPENXML(@i, '/MyApp/AppTrans',2)
WITH (
ApplicationID int,
TransactionID int,
BrowserID int '../BrowserID'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources