Remove fixed-sized part of an xml

Asked

Viewed 271 times

1

People, I have this piece of an XML and I need to remove the value (357122.40) that is between the tags < vMerc>xxxxxx< /vMerc>. Only this value has no fixed size.

...<infCTeNorm><infCarga><vMerc>357122.40</vMerc><proPred>...

I tried doing this SQL:

SUBSTRING((CONVERT(VARCHAR(MAX), xml_conhecimento)), (PATINDEX('%<infCTeNorm><infCarga><vMerc>%', CONVERT(VARCHAR(MAX), xml_conhecimento)) + 30), (((PATINDEX('%</vMerc><proPred>%', CONVERT(VARCHAR(MAX), xml_conhecimento))) - (PATINDEX('%<infCTeNorm><infCarga><vMerc>%', CONVERT(VARCHAR(MAX), xml_conhecimento))))-30))

But it does not work in table with many records. Appearing the following error:

Message 537, Level 16, State 3, Line 56 Invalid length parameter passed to LEFT or SUBSTRING function.

sql sql-server xml

  • 2 questions: Could you provide the entire XML? Would you tell us in which XML your SQL fails?

  • Following is an xml link and I don’t know where it fails: https://www.webdanfe.com.br/danfe/exemplos/CTe_signed.html

1 answer

1

This error means the function SUBSTRING is receiving a negative value in the parameter start or in the parameter length.

If the desired value appears always between tags <vMerc></vMerc> , is here a solution that always works. Returns NULL if you find no value.

I used variables only to make it easier to read and test the code, replace accordingly.

declare @xml        nvarchar(max)
declare @tagAbrir   nvarchar(200)
declare @tagFechar  nvarchar(200)
declare @pattern    nvarchar(max)
declare @inicio     int
declare @fim        int
declare @compriNum  int

set @xml        = '<infCTeNorm><infCarga><vMerc>357122.40</vMerc><proPred>'
set @tagAbrir   = '<vMerc>'
set @tagFechar  = '</vMerc>'
set @pattern    = '%'+ @tagAbrir + '%' + @tagFechar + '%'


set @inicio     = CASE WHEN PATINDEX(@pattern, @xml)>0 
                  THEN PATINDEX(@pattern, @xml) + LEN(@tagAbrir) 
                  ELSE NULL END
set @fim        = CHARINDEX(@tagFechar,@xml,@inicio)
set @compriNum  = @fim + LEN(@tagFechar) - @inicio - LEN(@tagAbrir)

select SUBSTRING(@xml,@inicio,@compriNum-1) AS Valor

Browser other questions tagged sql sql-server xml

You are not signed in. Login or sign up in order to post.