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

Question

I'm trying to insert rows into a table using a select from XML. I think I'm close. Where am I going wrong?

declare @xmldata xml;
set @xmldata = '<Database>
                  <PurchaseDetails>
                    <PurchaseDetail>
                      <Upc>72594206916</Upc>
                      <Quantity>77</Quantity>
                      <PurchaseDate>9/2010</PurchaseDate>
                      <PurchaseCity>Dallas</PurchaseCity>
                      <PurchaseState>TX</PurchaseState>
                    </PurchaseDetail>
                    <PurchaseDetail>
                      <Upc>72594221854</Upc>
                      <Quantity>33</Quantity>
                      <PurchaseDate>12/2013</PurchaseDate>
                      <PurchaseCity>Nashville</PurchaseCity>
                      <PurchaseState>TN</PurchaseState>
                    </PurchaseDetail>
                  </PurchaseDetails>
                </Database>'

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

Solution 1

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)

Solution 2

Try this!
query() then value()
run this in SQL Server and 100% worked
put a dot (.) first then the child tag.
PurchaseDetail tag exists 2 times so the dot (.) replaces the first and the second tag.
The dot can prevent using of [1] on XQuery.
The dot represents the first and the second PurchaseDetail tags.

INSERT INTO PurchaseDetails(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
SELECT col.query('./Upc').value('.', 'char(11)'),
    col.query('./Quantity').value('.', 'int'),
    col.query('./PurchaseDate').value('.', 'varchar(7)'),
    col.query('./PurchaseCity').value('.', 'varchar(50)'),
    col.query('./PurchaseState').value('.', 'char(2)')
FROM @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as ref(col)

It is more simplified query so far.
See if it works

Solution 3

insert into PurchaseDetails(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select T.X.value('(Upc/text())[1]', 'char(11)'),
       T.X.value('(Quantity/text())[1]', 'int'),
       T.X.value('(PurchaseDate/text())[1]', 'varchar(7)'),
       T.X.value('(PurchaseCity/text())[1]', 'varchar(50)'),
       T.X.value('(PurchaseState/text())[1]', 'char(2)')
from @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as T(X)

Solution 4

select
    x.Rec.query('./Upc').value('.','char(11)')
    ,x.Rec.query('./Quantity').value('.','int')
    ,x.Rec.query('./PurchaseDate').value('.','varchar(7)')
    ,x.Rec.query('./PurchaseCity').value('.','varchar(50)')
    ,x.Rec.query('./PurchaseState').value('.','char(2)')
from @xmlData.nodes('/Database/PurchaseDetails/PurchaseDetail') as x(Rec)

Solution 5

Struggling with a similar problem, and found that @birdus's answer didn't work if you have additional layers of nesting in your xml that you were referencing in your XQuery, e.g. supposing a slightly different XML shape, if you had

T.x.value('PurchasePlace/PurchaseCity[1]','varchar(50)')

you would still get the singleton error. Though @birdus's solution does work for this specific case a more generally applicable solution that combines the best of @birdus's & @Mikael-Eriksson's solution is to do:

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

This combine's @birdus's omission of /text(), which is superfluous, but adds @Mikael-Eriksson's parentheses around the element selector, to allow multiple element selectors as in my modified example which becomes:

T.x.value('(PurchasePlace/PurchaseCity)[1]','varchar(50)')

The reason for this, that a few have asked about, is not that @birdus's version returns something other than a singleton in any of the examples discussed here, but that it might. Per Microsoft Docs:

Location steps, function parameters, and operators that require singletons will return an error if the compiler cannot determine whether a singleton is guaranteed at run time.

View more solutions

Comments

[Jerod Venema]

Works perfectly; any idea what the reason behind the [1] is?

[Jami]

@jvenema, The reason behind the problem is that XQuery always returns a collection if you don't use [] at the end of it. So in your case pd.Value('sth') returns a collection, although nodes function in the From clause deliver only one row to the Value function By the way The Mikel's answer is also correct and more to the point.

[Anov]

Any idea what the cross apply is doing? x(Rec) isn't referenced in the query, but taking that piece out makes it not work.

[Dave Kelly]

This was surprisingly hard to find, works great..I don't understand how it works for unknown amount of records, is it recursive?

[Dan]

I'm having a similar issue but for what it's worth, I removed the /text(), and it caused a major lag in time in my data set. With /text(), it took 25 seconds to insert 55K records into the table. Without /text(), I stopped the query at 53 seconds because the additional time wasn't worth it.

[DubMan]

Wrapping the xpath in parenthesis worked for me, followed by the first instance attribute: XmlPayLoad.value( '(/root/child/wantedvalue)[1]','varchar' )

[max]

This is the most elegant and efficient solution that should be the accepted answer.