How to check for tag existence in XML colum

Naomi Nosonovsky 525 Reputation points
2024-06-25T23:01:38.5833333+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,345 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,731 Reputation points
    2024-06-26T01:24:48.3+00:00

    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;
    
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful