I need to search inside a field where I store an XML
The XML is from a CT-e and has a series of tags in it and I need to find a data inside a specific tag.
Below the table structure
TheXMLhasthefollowingstructureandwithinitthefieldIneedtoqueryisthe"Key" tag.
Is it possible to search, where I return the field PassKey but in the WHERE clause I put to fetch the value of the key I'm looking for inside the xml?
Thank you very much
SQLServer has some functions for extracting data from tables with xml fields.
I did a test creating a table containing an id field and another to store the XML:
create table cte (
id INTEGER IDENTITY,
campo_xml XML
)
Then I include some records with xml in the same structure below, just 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 the 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 querying the data you can create indexes for this XML field, since you will only need a part of the data. In the Microsoft documentation there is some usage and syntax information for the creation:
You'd have to do something of the sort
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'm passing a key to be located in your xml ef4dbf9f4b6c80fe03671cc476b29636
You need to 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 SUBSTRING which receives 3 parameters the first is its xml field from its table the second the initial index, ie what I had after <chave> the third parameter and the size of the string you want, here I'm assuming you have a string always the length of 32 characters, if it is different see how to search for the final index.