Xml Query() Works, Value() Requires Singleton Found Xdt:Untypedatomic

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

A co-worker had tackled a similar problem before. Here is what we came up with. NOT intuitive!

insert into PurchaseDetails
(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select
    pd.value('Upc[1]','char(11)'),
    pd.value('Quantity[1]','int'),
    pd.value('PurchaseDate[1]','varchar(7)'),
    pd.value('PurchaseCity[1]','varchar(50)'),
    pd.value('PurchaseState[1]','char(2)')
from @xmlData.nodes('//Database/PurchaseDetails') as x(Rec)
cross apply @xmlData.nodes('//Database/PurchaseDetails/PurchaseDetail') as i(pd)

XML query() works, value() requires singleton found xdt:untypedAtomic

You need to use this:

SELECT 
        x.requestpayload.value('declare namespace s="http://blah.ca/api";
            (/s:validate-student-request/s:student-id)[1]', 'int') 
    AS
        studentid
    FROM 
        xoutput x

You need to put your XPath in ( ... ) and add a [1] to simply select the first value of that sequence.

Xquery requires a singleton :the column name is an attribute

Try this:

select 
    Date_of_Birth = pd.value('(b[@nm="DATE OF BIRTH"])[1]', 'varchar(50)'),
    Driver = pd.value('(b[@nm="DRIVER"])[1]', 'varchar(50)')
from 
    @xml.nodes('/root/a') as i(pd)

Basically, you select all the <a> elements under <root> and for each of those elements, you grab the contained <b> element with either of the two @nm attributes to get the detailed info.

Produces the desired output for me.

Select values from XML in SQL

Maybe something like this:

DECLARE @xml XML
SET @xml='<customers>
        <customer>
            <kunnr>1</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
        <customer>
            <kunnr>2</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
    </customers>'

And then a query like this:

SELECT
    c.value('kunnr[1]', 'nvarchar(10)') AS kunnr,
    c.value('kdgrp[1]', 'nvarchar(10)') AS kdgrp
FROM
    @xml.nodes('//customers/customer') as t(c)

This will give you this result:

kunnr  kdgrp
1      2
2      2

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

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.

Transfer Views - 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

The error that occurred when trying to create the above-mentioned view was caused by a table column the view is referencing.

Before migrating all views of the AdventureWorks2014 database, I used a custom T-SQL script to generate table create statements to migrate tables similarly to the views. A bug in the script caused XML columns to be created as something different from an XML column, which explains why the access to one such column (AdditionalContactInfo of table Person.Person) that was expected to be an XML column was causing this error.

local-name()' requires a singleton (or empty sequence) T-SQL Xquery

One more set of parenthesis gives you a singleton as required:

DECLARE @t TABLE(s XML);
INSERT INTO @t(s)VALUES(N'<s1:DB2Request xmlns:s1="XYZDB2">
  <s1:sync>
    <s1:after identityInsert="false">
      <s1:PG204AT5>
        <s1:ISA06>1975111</s1:ISA06>
           <somethingsomething/>
      </s1:PG204AT5>
    </s1:after>
  </s1:sync>
</s1:DB2Request>');

select 
    s.value(
        'local-name((//*[local-name()="after"][1]/*)[1])',  
        'varchar(32)'
    ) as XmlTableName
from @t;

Results in:

XmlTableName
PG204AT5

SQL Server variable give me singleton error

That's possibly because sql:variable() returns xdt:anyAtomicType, I'm not sure. But this is one possible alternative, by using query() first, which enforce no limitation of returning only singleton, and then convert the return value to varchar(50) using value() function :

select @CurrentSrc = AssignmentDistribution
                         .query('(/*:md/*:dist/*:ml/*:src/text())[sql:variable("@i")]')
                         .value('.', 'varchar(50)')
FROM table where id = 1234

Why do we use [1] behind an order by clause in xquery expressions?

XQuery 1.0 defined an option called "static type checking": if this option is in force, you need to write queries in such a way that the compiler can tell in advance ("statically") that the result will not be a type error. The operand of "order by" needs to be a singleton, and with static type checking in force, the compiler needs to be able to verify that it will be a singleton, which is why the [1] has been added. It would have been better to write exactly-one($x/year) because this doesn't only keep the compiler happy, it also demands a run-time check that $x/year is actually a singleton.

Very few XQuery vendors chose to implement static type checking, for very good reasons in my view: it makes queries harder to write, and it actually encourages you to write things like this example that do LESS checking than a system without this "feature".

In fact, as far as I know the only mainstream (non-academic) implementation that does static type checking is Microsoft's SQL Server.

Static type checking should not be confused with optimistic type checking where the compiler tells you about things that are bound to fail, but defers checking until run-time for things that might or might not be correct.

Actually the above is a bit of a guess. It's also possible that some <plane> elements have more than one child called <year>, and that you want to sort on the first of these. That would justify the [1] even on products that don't do static type checking.

---

Related Topics