Querying XML field in database table using SQL

Question

I have a table named VehicleHistoryBlob that has the following structure:

VehicleHistoryBlobId int PRIMARY KEY
VehicleHistoryBlob XML

I need to write SQL that finds all entries in VehicleHistoryBlob XML that have Bus as a parent node and Destination as a child node (Bus can have many Destinations, and the parent node in the XML is not always a Bus).

<Bus>
...
    <Destination>
            <Name>The big building</Name>
            <DestinationCode> A21301423 </DestinationCode>
            <DestinationAddress> 440 Mountain View Parade </DestinationAddress>
            <DestinationCountry> USA </DestinationCountry>
    </Destination>
</Bus>'

I need to query through the XML and find all entries that have Bus as a parent node and Destination as a child node, and pass the VehicleHistoryBlobId associated with the XML into my temporary table @tmpTable

DECLARE @tmpTable TABLE(theints INT)

I have been trying to manipulate the .nodes function but I am struggling to yield accurate results due to my lack of experience with XML as a data type.

Thanks in advance!

har07 · Accepted Answer · 2017-02-25 11:50:15Z

2

To filter row by certain condition on the XML column, you can use exist() method instead of nodes(). For example, the following query insert to @temptable VehicleHistoryBlobId where corresponding XML has Bus as root element and Destination child element :

INSERT INTO @tmpTable
SELECT v.VehicleHistoryBlobId 
FROM VehicleHistoryBlob v
WHERE v.VehicleHistoryBlob.exist('/Bus/Destination') = 1

sqlfiddle demo

answered Feb 25, 2017 at 11:50

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Over a year ago

Awesome! Thank you so much @har07

Collectives™ on Stack Overflow

Querying XML field in database table using SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related