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) 

Answer 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) 

Answer 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