How to extract data from XML field excluding specific attribute values

Data and Databases
1

I’m working with a database table that has an XML field storing product information. The XML structure looks like this:

<Products>
    <Product Active="Yes" ProductId="101" Order="1" Archived="No">Electronics</Product>
    <Product Active="Yes" ProductId="102" Order="2" Archived="Yes">Books</Product>
    <Product Active="No" ProductId="103" Order="3">Clothing</Product>
    <Product Active="Yes" ProductId="104" Order="4" Archived="Yes">Sports</Product>
    <Product Active="Yes" ProductId="105" Order="5">Home Goods</Product>
</Products>

I need to write a SQL query that will pull only the products where Archived is not set to “Yes”. I’m pretty new to working with XML in SQL queries and could really use some guidance on the proper syntax and approach.

2

oh interesting xml challenge! are you using sql server or maybe oracle? the syntax can vary quite a bit between databases. also curious - when the Archived attribute is completely missing (like in your third product), do you want to include those too or exclude them? that might affect how you structure the xpath query.

3

I encountered this exact scenario when working with legacy product catalogs. The key is using XPath with proper null handling since some products lack the Archived attribute entirely. For SQL Server, you want to extract products where the Archived attribute either doesn’t exist or isn’t set to “Yes”. Try using the nodes() method combined with value() to filter: SELECT T.C.value('@ProductId', 'INT') as ProductId, T.C.value('.', 'VARCHAR(50)') as ProductName FROM your_table CROSS APPLY xml_column.nodes('/Products/Product[@Active="Yes" and (@Archived != "Yes" or not(@Archived))]') T(C). This approach handles both missing Archived attributes and explicitly checks for non-“Yes” values, which solved similar filtering requirements in my experience.

4

assuming youre on sql server, try something like SELECT * FROM your_table WHERE xml_column.exist('/Products/Product[@Archived="Yes"]') = 0. but honestly this gets tricky with missing attributes - might need to handle those seperately depending on what you want to do with nulls.