XPath 'contains()' requires a singleton (or empty sequence)

Question

Given the XML:

<Dial>
    <DialID>
        24521
    </DialID>
    <DialName>
        Base Price
    </DialName>
</Dial>
<Dial>
    <DialID>
        24528
    </DialID>
    <DialName>
        Rush Options
    </DialName>
    <DialValue>
        1.5
    </DialValue>
</Dial>
<Dial>
    <DialID>
        24530
    </DialID>
    <DialName>
        Bill Rush Charges
    </DialName>
    <DialValue>
        School
    </DialValue>
</Dial>

I can use the contains() function in my xpath:

//Dial[DialName[contains(text(), 'Bill')]]/DialValue

To retrieve the values I'm after:

School

The above XML is stored in a field in my SQL database so I'm using the .value method to select from that field.

SELECT Dials.DialDetail.value('(//Dial[DialName[contains(text(), "Bill")]]/DialValue)[1]','VARCHAR(64)') AS BillTo
FROM CampaignDials Dials

I can't seem to get the syntax right though... the xpath works as expected (tested in Oxygen and elsewhere) but when I use it in the XQuery argument of the .value() method, I get an error:

Started executing query at Line 1
Msg 2389, Level 16, State 1, Line 36
XQuery [Dials.DialDetail.value()]: 'contains()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
Total execution time: 00:00:00.004

I've tried different variations of single and double quotes with no effect. The error refers to an XPath data type for attributes, but I'm not retrieving an attribute; I'm getting the text value. I receive the same error if I type the response with //Dial[DialName[contains(text(), 'Bill')]]/DialValue/text() instead.

What is the correct way to use contains() in an XQuery when it's used in the XML.value() method? Or is this the wrong approach to begin with?

While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Mar 02 '21 at 18:59

score 1 · Accepted Answer · answered Mar 02 '21 at 20:21

1

You nearly have it right, you just need [1] on the text() function to guarantee a single value.

You should also use text() on the actual node you are pulling out, for performance reasons.

Also, // can be inefficient, so only use it if you really need recursive descent. You can instead use /*/ to get the first node of any name.

SELECT
    Dials.DialDetail.value(
        '(//Dial[DialName[contains(text()[1], "Bill")]]/DialValue/text())[1]',
        'VARCHAR(64)') AS BillTo
FROM CampaignDials Dials

As Yitzhak Kabinsky notes, this only gets you one value per row of the table, you need .nodes if you want to shred the XML itself into rows.

answered Mar 02 '21 at 20:21

Charlieface

52,284
6
19
43

This solved it. Thank you. In this case I'm certain there can't be more than one DialName so I didn't think to force it to only get the first. – Brian Dieckman Mar 02 '21 at 21:44
Yes it must be statically guaranteed to only have one result. Same issue with `.value` – Charlieface Mar 02 '21 at 21:45

score 0 · Answer 2 · answered Mar 02 '21 at 18:51

The difference between your actual database case that fails and your reduced sample case that works is likely one of different data.

The error,

contains() requires a singleton (or empty sequence)

indicates that one of your DialName elements has multiple text node children rather than a single text node child as you're expecting.

You can abstract away such variations by testing the string-value of DialName rather than its text node children:

//Dial[contains(DialName, 'Bill')]/DialValue

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, DialDetail XML);
INSERT INTO @tbl (DialDetail) VALUES
(N'<Dial>
        <DialID>24521</DialID>
        <DialName>Base Price</DialName>
    </Dial>
    <Dial>
        <DialID>24528</DialID>
        <DialName>Rush Options</DialName>
        <DialValue>1.5</DialValue>
    </Dial>
    <Dial>
        <DialID>24530</DialID>
        <DialName>Bill Rush Charges</DialName>
        <DialValue>School</DialValue>
    </Dial>');
-- DDL and sample data population, end

SELECT ID
    , c.value('(DialID/text())[1]', 'INT') AS DialID
    , c.value('(DialName/text())[1]', 'VARCHAR(30)') AS DialName
    , c.value('(DialValue/text())[1]', 'VARCHAR(30)') AS DialValue
FROM @tbl CROSS APPLY DialDetail.nodes('/Dial[contains((DialName/text())[1], "Bill")]') AS t(c);

Output

+----+--------+-------------------+-----------+
| ID | DialID |     DialName      | DialValue |
+----+--------+-------------------+-----------+
|  1 |  24530 | Bill Rush Charges | School    |
+----+--------+-------------------+-----------+

XPath 'contains()' requires a singleton (or empty sequence)

3 Answers3

See also