CODEWITHSUNDEEP
sqlsql-serverxmldatabaset-sql
Mark
Mark

Reputation: 531

Querying XML field in database table using SQL

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!

Upvotes: 0

Views: 44

Answers (1)

har07
har07

Reputation: 89285

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

Upvotes: 2

Related Questions