Find if a list contains several values - SQL Server Xquery

Question

I am storing a XML data into a table called BikeTable. The XML data is coming from an object that is being serialized using .Net serializer. BikeTable would look like this :

Id - UniqueIdentifier
XmlData - XML

The XML stored in the XmlData column looks like this :

Record 1 :

<Bike>
    <Material>
        <Cage>EIECH</Cage>
        <Mpn>B258-C436-B001</
    </Material>
    <Roles>
        <string>Race</string>
        <string>Mountain</string>
        <string>City</string>
    </Roles>
</Bike>

Record 2 :

<Bike>
    <Material>
        <Cage>ABCDE</Cage>
        <Mpn>B258-C436-B001</Mpn>
    </Material>
    <Roles>
        <string>Race</string>
    </Roles>
</Bike>

I want to be able to find the records in my table that will contain for example Race and Mountain.

Example if I want the Ids of the record that contains 'Road'and 'Mountain" the only way I found is like this :

select Id 
from BikeTable
where XmlData.exist('/Bike/Roles/string[contains(., "Road")]') = 1 
   or XmlData.exist('/Bike/Roles/string[contains(., "Mountain")]') = 1

I don't like this option because it forces me to generate the query if I want to find records that would match one or several roles. Roles can contains unlimited number of values and I need to be able to find the records that will one or more values. Ex : records containing Race, records containing Race or Montain, records containing City, records containing City and Mountain etc.

Is there any way to know if a list contains several values?

Thom A · Accepted Answer · 2018-05-14T18:34:02.113

0

Yes, you can. This is a bit of a guess though, as you say you want to do a SELECT *; something that is impossible to provide any data for without the DDL of the table. Thus, instead, I've returned the Cage and Mpn of the Bike:

CREATE TABLE BikeTable (xmlData xml);

--The Close tag for Mpn was missing in your sample data, I assume it wasn't mean to be
INSERT INTO BikeTable
VALUES('<Bike>
    <Material>
        <Cage>EIECH</Cage>
        <Mpn>B258-C436-B001</Mpn>
    </Material>
    <Roles>
        <string>Race</string>
        <string>Mountain</string>
        <string>City</string>
    </Roles>
</Bike>')
GO
WITH Bikes AS (
    SELECT B.Material.value('(Cage/text())[1]','varchar(15)') AS Cage, --Data Type guessed
           B.Material.value('(Mpn/text())[1]','varchar(15)') AS Mpn, --Data Type guessed
           BR.String.value('(./text())[1]','varchar(15)') AS String --Data Type guessed
    FROM BikeTable BT
         CROSS APPLY BT.xmlData.nodes('/Bike/Material') B(Material)
         CROSS APPLY BT.xmlData.nodes('/Bike/Roles/string') BR(String))
SELECT Cage, Mpn
FROM Bikes
GROUP BY Cage, Mpn
HAVING COUNT(String) > 1;

GO

DROP TABLE BikeTable;

edited May 14 '18 at 18:34

answered May 14 '18 at 18:28

Thom A

88,727
11
45
75

Thanks for the asnwer. I think I did not explain my problem very well, or I did not understand your query. I don't see how I can find a record containing a specific value like 'Mountain' or 'Mountain OR Road' – Guigui May 14 '18 at 18:47
Your question was *"Is there any way to know if a list contains several values?"*, which is what that query does: It returns values that have multiple values for `string`. This is why expected results are really important. Perhaps you need to now use a `WHERE` and `IN`, and get rid of the `GROUP BY` and `HAVING`. Update your question with more detail, and let me know, and I'll be happy to look; but I can't make more guesses than I already have. :) – Thom A May 14 '18 at 18:50
Yes sorry about that, my question was not clear enough. I updated it with more details. And using your request and your comment I was able to have a first result. One thing I wondering now is the performance. – Guigui May 14 '18 at 18:58
@Guigui Provided you're making use of `text()`, then the x-query should perform pretty well. If you don't use `text()`, then that actually adds extra overhead to the query (I can't recall the specific reason, apologies). – Thom A May 14 '18 at 19:00

Find if a list contains several values - SQL Server Xquery

1 Answers1