Search code examples
sql-serverxmlxpathxquery

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

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?

Solution

  • 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.