<?xml version="1.0" standalone="yes"?>
<AsOrders>
	<Cust Name='165237'>
	<Customers>
		<CustomerID>17335</CustomerID>
		<ContactName>Amy surname</ContactName>
		<Email>asurname@Email.com</Email>
	</Customers>
	<Order>
		<OrderNumber>165237</OrderNumber>
		<OrderDate>14/01/2013 13:36:33</OrderDate>
	</Order>
	<OrderItems>
		<Description>SALE - RED </Description>
		<Price>6.50</Price>
		<ProductCode>343</ProductCode>
		<ColourCodeId>N/A</ColourCodeId>
		<ColourCodeTitle>N/A</ColourCodeTitle>
		<Quantity>1</Quantity>
	</OrderItems>
           <OrderItems>
		<Description>SALE - BLUE </Description>
		<Price>6.50</Price>
		<ProductCode>344</ProductCode>
		<ColourCodeId>N/A</ColourCodeId>
		<ColourCodeTitle>N/A</ColourCodeTitle>
		<Quantity>1</Quantity>
	</OrderItems>
	</Cust>	
           <Cust Name='165236'>
	<Customers>
		<CustomerID>16154</CustomerID>
		<ContactName>Loretta surname</ContactName>
		<Email>Lorettasurname@email.com</Email>
	</Customers>
	<Order>
		<OrderNumber>165236</OrderNumber>
		<OrderDate>14/01/2013 13:23:57</OrderDate>
	</Order>
	<OrderItems>
		<Description>SAL GREEN</Description>
		<Price>72.00</Price>
		<ProductCode>274</ProductCode>
		<ColourCodeId>N/A</ColourCodeId>
		<ColourCodeTitle>N/A</ColourCodeTitle>
		<Quantity>2</Quantity>
	</OrderItems>
	</Cust>
</AsOrders>

CREATE TABLE XmlImportTest
(
    xmlFileName VARCHAR(300),
    xml_data xml
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT  @xmlFileName = 'E:\Orders.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData 
FROM
(
    SELECT  * 
    FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO

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,	
	C.value('(OrderItems/Description)[1]','varchar(120)') as ItemDesc,
	C.value('(OrderItems/Price)[1]','money') as ItemPrice,
	C.value('(OrderItems/ProductCode)[1]','INT') as ProdCode,
	C.value('(OrderItems/ColourCodeId)[1]','varchar(120)') as ColCode,
	C.value('(OrderItems/ColourCodeTitle)[1]','varchar(120)') as ColCodeTitle,
	C.value('(OrderItems/Quantity)[1]','float(20)') as ItemQty
	Into Tbl_NewTableName	
	from
xmlImportTest
cross apply 
xml_data.nodes('/DsOrders/Cust') as Cust(C)

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,	
	D.value('(Description)[1]','varchar(120)') as ItemDesc,
	D.value('(Price)[1]','money') as ItemPrice,
	D.value('(ProductCode)[1]','INT') as ProdCode,
	D.value('(ColourCodeId)[1]','varchar(120)') as ColCode,
	D.value('(ColourCodeTitle)[1]','varchar(120)') as ColCodeTitle,
	D.value('(Quantity)[1]','float(20)') as ItemQty
	Into Tbl_NewTableName	
	from
xmlImportTest
cross apply 
xml_data.nodes('/DsOrders/Cust') as Cust(C)
cross apply 
xml_data.nodes('/DsOrders/Cust/OrderItems') as Items(D)

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)