Search XML field information in SQL

Question

I need to perform a search inside a field where it stores an XML

The XML is from a CT-e and in it has a series of tags and I need to find a data within a specific tag.

Below the structure of the Table

XML has the following structure and within it the field I need to query is the tag "Key".

It is possible a search, where I return the Keyaccess field but in the WHERE clause I put to fetch the value of the key I’m looking for inside xml?

Thank you very much

Answer 1

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.

Answer 2

Sqlserver has some functions to extract data from tables with xml fields.

https://docs.microsoft.com/pt-br/sql/t-sql/xml/value-method-xml-data-type https://sqlfromhell.wordpress.com/2011/08/02/lendo-xml-no-sql-server-iniciando-com-xquery/ https://pessoalex.wordpress.com/2012/01/24/extraindo-informacoes-de-arquivo-xml-para-o-sql-server/

I did a test creating a table containing an id field and another to store XML:

create table cte (
    id INTEGER IDENTITY,
    campo_xml XML
)

Later I included some xml records in the same structure below, only modifying the CTE key:

insert into cte (campo_xml)
values (Cast(
    '<cteProc xmlns="http://www.portalfiscal.inf.br/cte" versao="2.00">
        <protCTe versao="1.03">
            <infProt>
                <tpAmb>2</tpAmb>
                <verAplic>SP_PL_CTe_103d</verAplic>
                <chCTe>99999994018888000349570040000165560001999999</chCTe>
                <dhRecbto>2011-07-05T14:42:56</dhRecbto>
                <nProt>135110011494444</nProt>
                <digVal>eFWH/VAzwb+MQuBSp9NWZdzXdL4=</digVal>
                <cStat>100</cStat>
                <xMotivo>Autorizado o uso do CT-e</xMotivo>
            </infProt>
        </protCTe>
    </cteProc>' as XML))

Finally I made the query to return the record matches the key in XML:

WITH XMLNAMESPACES(DEFAULT 'http://www.portalfiscal.inf.br/cte')
select * 
from cte
where 
    campo_xml.value('(/cteProc/protCTe/infProt/chCTe)[1]', 'varchar(100)' )='99999994018888000349570040000165560001999999'

To speed up the data query, indexes can be created for this XML field, since you will only need a part of the data. In the Microsoft documentation there is some information about the use and syntax for the creation:

https://docs.microsoft.com/pt-br/sql/relational-databases/xml/xml-indexes-sql-server https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-xml-index-transact-sql