You’d have to do something like
DECLARE @document varchar(max);
SELECT @document = '<Header><Header><Username>testeyyytttyyyyyy</Username><chave>ef4dbf9f4b6c80fe03671cc476b29636</chave></Header></Header>';
DECLARE @tabela TABLE
(
id int,
xml nvarchar(max)
)
INSERT INTO @tabela VALUES (1, @document);
SELECT @document = '<Header><Header><Username>testeyyytttyyyyyy</Username><chave>ef4dbf9f4b6c80fe03671cc476b29637</chave></Header></Header>';
INSERT INTO @tabela VALUES (1, @document);
SELECT @document = '<Header><Header><Username>testeyyytttyyyyyy</Username><chave>ef4dbf9f4b6c80fe03671cc476b29638</chave></Header></Header>';
INSERT INTO @tabela VALUES (1, @document);
SELECT * FROM @tabela AS t
WHERE 'ef4dbf9f4b6c80fe03671cc476b29636' =
(SELECT SUBSTRING(t.xml, (select PATINDEX('%<chave>%', t.xml) + len('<chave>')), 32))
Note that in WHERE I am passing a key to be located in your xml ef4dbf9f4b6c80fe03671cc476b29636
It is necessary that you find it inside your xml so that you can filter it later
(SELECT SUBSTRING(t.xml, (select PATINDEX('%<chave>%', t.xml) + len('<chave>')), 32))
I am using the SUBSTRING that receives 3 parameters the first is its xml field of its table the second the initial index , IE, what I had after the <chave>
the third parameter and the size of the string you want, here I am assuming you have a string always the size of 32 characters, if different see how to search for the final index.
the value is the same ?
– Marco Souza
No. The value of the "Keyaccess" field is the identifier of the document called CT-e. On the other hand, the key tag within XML refers to the key of the invoice that is being transported by Cte.
– Mauricio Barcelo da Costa
you want to make a filter to fetch the value inside xml, it will get expensive.
– Marco Souza
How to recover a value from within a tag in XML?
– Marconi