Hi ,
I am importing xml data into SQL server 2005 in this manner:
MyXML
<Orders>
<Order>
<OrderID>1</OrderID>
<OrderInfo>mydetails</OrderInfo>
<orderlines>
<orderline>
<orderlineref>1.1</orderlineref>
<product>myproduct</product>
<quantity>5</quantity>
</orderline>
<orderline>
<orderlineref>1.2</orderlineref>
<product>myproduct1</product>
<quantity>7</quantity>
</orderline>
</orderlines>
</Order>
</Orders>
My import code
insert into orders (orderid, orderinfo)
select
xmlquery.order.query('OrderID').value('.','int'),
xmlquery.order.query('OrderID').value('.','varchar(50)')
From (
select cast (xmlquery as XML) from openrowset(
Bulk 'C:\data\orders.xml',singleblob) as T(xmlquery)
) as T(xmlquery)
cross apply xmlquery.nodes('Orders/Order') as xmlquery(order)
insert into orderlines (orderlineref, product,quantity)
select
xmlquery.orderline.query('orderlineref').value('.','decimal(12,4)'),
xmlquery.orderline.query('product').value('.','varchar(50)'),
xmlquery.orderline.query('quantity').value('.','int')
From (
select cast (xmlquery as XML) from openrowset(
Bulk 'C:\data\orders.xml',singleblob) as T(xmlquery)
) as T(xmlquery)
cross apply xmlquery.nodes('Orders/Order/orderlines/orderline') as xmlquery(orderline)
This works fine but is very slow compared to similar imports that I have done on text files.
Is there a quicker way of doing this please? If so could you give an example. I have given up on msdn as it is more confusing than helpful on this matter.