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

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)