Remove part without fixed size from an xml

Question

Remove part without fixed size from an xml

Navigation

#1 by (1 votes)

1

Galera, I have this piece of XML and I need to remove the value (357122.40) that is between the < vMerc > xxxxxx < / vMerc > However, this value does not have a 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 on a table with many records. The following error appears:

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

sql sql-server xml

asked by anonymous 10.04.2015 / 20:15

1 answer

Procedure If exists update else insert in table with SQL Server composite key How to keep listview item loaded

score 1 · Answer 1

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

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

I used variables just 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