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