CODEWITHSUNDEEP
sqlsql-serverxmlxquery
Chandrasen Singh
Chandrasen Singh

Reputation: 93

How To retrieve value of an element from same name multiple elements in XML through xQuery?

I have and xml field like this in sql server

<propertyDetail>
  <importID>1735532</importID>
  <pincode />
  <landmarks />
  <features>
    <feature>Society  Name: sec-87 srs peral  floor faridabad</feature>
    <feature>Transaction: Resale Property</feature>
    <feature>Possession: Dec,2011</feature>
    <feature>*Ownership: Freehold*</feature>
    <feature>Age of Property: Under Construction</feature>
  </features>
</propertyDetail>

I want to retrieve the feature which has value "Ownership: Freehold" through xQuery and sequence of features may vary.

Welcome any suggestion.

Upvotes: 0

Views: 1246

Answers (2)

podiluska
podiluska

Reputation: 51494

Where @x is your xml

select @x.query('//feature[. = "*Ownership: Freehold*"]')

If you want to find anything that starts with Ownership then

select @x.query('//feature[substring(.,1,9)="Ownership"]')

(but ideally you should improve your XML structure, if you can)

Upvotes: 1

BeniBela
BeniBela

Reputation: 16907

You can check for the existence of that feature in a filter expression, i.e.:

//propertyDetail[features/feature[. eq "*Ownership: Freehold*"]]

Upvotes: 1

Related Questions