Tiriumndryday
New Member
This is the stored procedure I'm using to get 5 digits from an xml:\[code\] CREATE PROCEDURE [dbo].[SP_KINGPRICE_InsertJournalFromPost] ( @ResponseID bigint, @TransactionDetailID bigint ) AS BEGIN DECLARE @info as varchar(max) = '', @Reference as varchar(max) = '' SET @info = (SELECT SUBSTRING(Response, CHARINDEX('<GlJournal>',Response) + 11,5) FROM SysproIntegration..ifmTransactionDetailResponse WHERE TransactionDetailResponseID = @ResponseID) SET @Reference = (SELECT DISTINCT Reference FROM GenJournalDetail WHERE Journal = CAST(@info as DECIMAL(5,0))) INSERT INTO ZJournalRecords (JournalNumber,Reference) VALUES (@info,@Reference) END\[/code\]The XML has a tag like this:\[code\] <GLJournal>12345</GLJournal>\[/code\]If the XML document has only one of these tags, I have no worries. The SP works fine. The problem comes in when there are two nested \[code\]<GLJournal>\[/code\] tags. The xml would then look something like:\[code\] <GLJournal> <SomeTag/> <SomeTag2/> <GLJournal>12345</GLJournal> </GLJournal>\[/code\]How can I get the 5 digit value of the nested tag? (It will always be 5 digits)I have thought of using a try catch, but that doesn't seem like an elegant solution.EDIT:
Also, part of the problem is, I don't know when there will be one \[code\]GlJournal\[/code\] tags, or two.
Also, part of the problem is, I don't know when there will be one \[code\]GlJournal\[/code\] tags, or two.