Click here to Skip to main content
14,268,797 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a table with an XML Column called Vehicle.

The XML looks like this:

<vehicle>
 <sedan>
  <type color="red" make="honda" year="2019">
   <dealers>
    <dealer address="1234 Cat Drive">
    <dealer address="1944 Dog Drive">
   </dealers>
  </type>
 </sedan>
</vehicle>


For example, I want to select a row based on the address value:
How do I select all data from the XML where the address equals
1234 Cat Drive


What I have tried:

So far what I tried is using the query method.
But there has to be a better way, I also see a lot of web examples but nothing specific to how I can get a row by passing a value.

SELECT Vehicles.query('/vehicles/sedan/type/dealers/dealer[@address="1944 Dog Drive"]')
Posted
Updated 13-Aug-19 23:53pm
Rate this:
Please Sign up or sign in to vote.

Solution 1

   
Rate this:
Please Sign up or sign in to vote.

Solution 2

If you just want to select the rows from the table where that address exists within the XML column:
DECLARE @AddressToFind nvarchar(100) = N'1234 Cat Drive';

SELECT
    Vehicle,
    OtherColumns
FROM
    YourTable
WHERE
    Vehicle.exist('//dealer[@address = sql:variable("@AddressToFind")]') = 1
;
exist() Method (xml Data Type) - SQL Server | Microsoft Docs[^]
Binding Relational Data Inside XML Data - SQL Server | Microsoft Docs[^]

If you want to return parts of the XML document, you can either use query or nodes to shred the document.

query() Method (xml Data Type) - SQL Server | Microsoft Docs[^]
nodes() Method (xml Data Type) - SQL Server | Microsoft Docs[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100