search on xml field in table of sql server?

[Tridip Bhattacharjee]

suppose i have few fileds in table and one filed is xml type. how to search this. the xml which is stored in my xml filed like <OpenShipments xmlns="x-schema:C:\UPSLabel\OpenShipments.xdr"> <OpenShipment ShipmentOption="RS" ProcessStatus=""> <ShipTo> <CompanyOrName>CRESCENT AUTO SERVICES LTD</CompanyOrName> <Attention>ZULFI</Attention> <Address1>16-18 THE CRESCENT</Address1> <Address2 /> <Address3>SOUTHALL</Address3> <CountryTerritory>GB</CountryTerritory> <PostalCode>UB1 1BE</PostalCode> <CityOrTown>SOUTHALL</CityOrTown> <StateProvinceCounty>SOUTHALL</StateProvinceCounty> <Telephone>02085719998/02085742845</Telephone> </ShipTo> <ShipmentInformation> <ServiceType>UPS Standard</ServiceType> <PackageType /> <NumberOfPackages>1</NumberOfPackages> <ShipmentActualWeight>5</ShipmentActualWeight> <DescriptionOfGoods>Car Parts</DescriptionOfGoods> <BillingOption>PP</BillingOption> <ReturnService> <Options>PRL</Options> <MerchandiseDescOfPackage>OLD CORE RETURN</MerchandiseDescOfPackage> </ReturnService> </ShipmentInformation> </OpenShipment> </OpenShipments> we can query sql server data very easily but i dont know how to query the xml data which stored in xml filed. how to query address field in my xml or how to query Options field,how query CompanyOrName. please help me to construct the sql. guide me in detail with few sample for querying xml datatype. thanks in advance

tbhattacharjee

[Pranay Rana]

Try this http://www.15seconds.com/Issue/050803.htm[^]

[Lost User]

Tridip Bhattacharjee wrote:

how to query address field in my xml or how to query Options field,how query CompanyOrName.

A XML-query will look somewhat more complicated then a "regular" query. I hope that the example below provides a good impression of the used construct;

DECLARE @ShipXML XML
SET @ShipXML =
'<OpenShipments>
<OpenShipment>
<NumberOfPackages>1</NumberOfPackages>
<ShipmentActualWeight>5</ShipmentActualWeight>
<DescriptionOfGoods>Car Parts</DescriptionOfGoods>
</OpenShipment>
<OpenShipment>
<NumberOfPackages>2</NumberOfPackages>
<ShipmentActualWeight>3</ShipmentActualWeight>
<DescriptionOfGoods>Bike Parts</DescriptionOfGoods>
</OpenShipment>
</OpenShipments>'

SELECT shs.sh.value('NumberOfPackages[1]','bigint') AS NumberOfPackages
,shs.sh.value('ShipmentActualWeight[1]','bigint') AS ShipmentActualWeight
,shs.sh.value('DescriptionOfGoods[1]','varchar(20)') AS DescriptionOfGoods
FROM @ShipXML.nodes('OpenShipments/OpenShipment') shs(sh)
WHERE shs.sh.exist('.[DescriptionOfGoods != "Car Parts"]') = 1

The example was built using sample-code from MSDN[^].

I are Troll :suss: