CODEWITHSUNDEEP
sqlxml
Jni
Jni

Reputation: 172

How to retrieve multiple values of a Xml node in Sql Server 2008

Look this Xml

<Criteres>
   <TypesOffre>
      <TypeOffre>Appartement</TypeOffre>
      <TypeOffre>Maison</TypeOffre>
   </TypesOffre>
</Criteres>

I would like to retrieve the values of TypeOffre node and use them in a JOIN like the following statement

SELECT T2.Nom
FROM RechercheOffre T1
     INNER JOIN TypeOffre T2 ON (T1.Xml.value('(/Criteres/TypesOffre/TypeOffre)[1]', 'varchar(max)') = T2.Nom)

I can't use the value method because she needs a single instance ...

and the query method returns the values of the Typeoffre node in a single column like this 

AppartementMaison

Any idea ?

Upvotes: 0

Views: 5959

Answers (2)

Jni
Jni

Reputation: 172

With a JOIN

SELECT T2.Nom
FROM RechercheOffre T1
CROSS APPLY T1.Xml.nodes('/Criteres/TypesOffre/TypeOffre') AS CTT(TypeOffre)
INNER JOIN TypeOffre T2 ON (TypeOffre.value('(.)', 'varchar(50)') = T2.Nom)

Thank's marc_s

Upvotes: 0

marc_s
marc_s

Reputation: 754268

Try this:

SELECT T2.Nom
FROM RechercheOffre T1
CROSS APPLY T1.Xml.nodes('/Criteres/TypesOffre/TypeOffre') AS CTT(TypeOffre)
WHERE 
  T2.Nom = TypeOffre.value('(.)', 'varchar(50)')

You need to use CROSS APPLY on the .nodes() XQuery function to get a pseudo list of rows of XML, and then you can pull out the individual values from the XML nodes and compare to other bits of your database.

Upvotes: 3

Related Questions