How to check for tag existence in XML colum

Question

Good day,

I have a complex XML stored in a table's column which may or may not contain the following tag <D_STC12_FreeFormMessageText> How can I select all rows containing this tag? Example from one of XMLs:

<D_STC12_FreeFormMessageText>H MISSING OR INVALID INFORMATION. ADDITIONAL DETAILS WILL FOLLOW IF AVAILABLE</D_STC12_FreeFormMessageText>

The XML starts like this:

<Interchanges>
  <S_ISA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="www.edifabric.com/x12">
    <D_744_1>00</D_744_1>
    <D_745_2 />
    <D_746_3>00</D_746_3>
    <D_747_4 />
    <D_704_5>ZZ</D_704_5>
    <D_705_6>841162764      </D_705_6>
    <D_704_7>ZZ</D_704_7>
    <D_706_8>GATEDN12       </D_706_8>
    <D_373_9>230613</D_373_9>
    <D_337_10>0810</D_337_10>
    <D_726_11>^</D_726_11>
    <D_703_12>00501</D_703_12>
    <D_709_13>164081021</D_709_13>
    <D_749_14>0</D_749_14>
    <D_748_15>P</D_748_15>
    <D_701_16>:</D_701_16>
  </S_ISA>
  <S_GS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="www.edifabric.com/x12">

Thanks in advance.

Answer 1

Hi @Naomi Nosonovsky,

The XQuery .exist() method to the rescue.

Notable points:

• We are using a wildcard XML namespace in the XPath expression because we don't know your true XML structure and its namespaces.
• XPath predicate [text()] will retrieve rows where D_STC12_FreeFormMessageText XML element present and has values, i.e. rows where XML element has blank value won't be retrieved.

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<Interchanges>
	<S_ISA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
	       xmlns="www.edifabric.com/x12">
		<D_744_1>00</D_744_1>
		<D_745_2/>
	</S_ISA>
	<S_GS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
	      xmlns="www.edifabric.com/x12">
		<D_STC12_FreeFormMessageText>H MISSING OR INVALID INFORMATION. ADDITIONAL DETAILS WILL FOLLOW IF AVAILABLE</D_STC12_FreeFormMessageText>
	</S_GS>
</Interchanges>'),
(N'<Interchanges>
	<S_ISA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
	       xmlns="www.edifabric.com/x12">
		<D_744_1>00</D_744_1>
		<D_745_2/>
	</S_ISA>
	<S_GS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
	      xmlns="www.edifabric.com/x12">
	</S_GS>
</Interchanges>');
-- DDL and sample data population, end
SELECT * 
FROM @tbl
WHERE xmldata.exist('//*:D_STC12_FreeFormMessageText[text()]') = 1;