使用SQL查询数据库表中的XML字段

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

我有一个名为VehicleHistoryBlob的表,它具有以下结构:

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).

我需要编写SQL,找到VehicleHistoryBlob XML中的所有条目,其中Bus作为父节点,Destination作为子节点(Bus可以有多个Destinations,XML中的父节点并不总是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

我需要查询XML并查找所有将Bus作为父节点并将Destination作为子节点的条目,并将与XML关联的VehicleHistoryBlobId传递到我的临时表@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.

我一直试图操纵.nodes函数,但由于我缺乏XML作为数据类型的经验,我很难得到准确的结果。

Thanks in advance!

提前致谢!

1 个解决方案

#1

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 :

要在XML列上按特定条件过滤行,可以使用exist()方法而不是nodes()。例如,以下查询插入到@temptable VehicleHistoryBlobId,其中对应的XML将Bus作为根元素和Destination子元素:

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

sqlfiddle demo