Hi,

I think you are very close, does this updated final query do what you need it to do or get you closer?

select

C.value('(Customers/CustomerID)[1]','INT') as CusID,

C.value('(Customers/ContactName)[1]','varchar(50)') as ContactName,

C.value('(Customers/Email)[1]','varchar(150)') as eml,

C.value('(Order/OrderNumber)[1]','INT') as OrderNo,

C.value('(Order/OrderDate)[1]','varchar(20)') as OrderDate,

i.value('(Description)[1]','varchar(120)') as ItemDesc,

i.value('(Price)[1]','money') as ItemPrice,

i.value('(ProductCode)[1]','INT') as ProdCode,

i.value('(ColourCodeId)[1]','varchar(120)') as ColCode,

i.value('(ColourCodeTitle)[1]','varchar(120)') as ColCodeTitle,

i.value('(Quantity)[1]','float(20)') as ItemQty

Into Tbl_NewTableName

from

xmlImportTest

cross apply xml_data.nodes('/AsOrders/Cust') as Cust(C)

CROSS APPLY C.nodes('OrderItems') AS Items(I)

This returns three records, two for order 165237 and one for order 165236