How do you read an XML file in SQL?

Question

I am using SQL Server 2014 to query XML text using xquery. I am able to insert portions of XML text to query but I need to be able to point to a local file to read and query it and cannot figure out how to do that. Below is what I have for what I am currently doing.

declare @xmldata xml    
set @xmldata = '
<Orders>
  <Order OrderID="100" OrderDate="1/30/2012">
   <OrderDetail ProductID="1" Quantity="3">
    <Price>350</Price>
   </OrderDetail>
   <OrderDetail ProductID="2" Quantity="8">
    <Price>500</Price>
   </OrderDetail>
   <OrderDetail ProductID="3" Quantity="10">
    <Price>700</Price>
   </OrderDetail>
  </Order>
  <Order OrderID="200" OrderDate="2/15/2012">
   <OrderDetail ProductID="4" Quantity="5">
    <Price>120</Price>
   </OrderDetail>
  </Order>
 </Orders>'

SELECT  x.c.value('(OrderDetail)[2]', 'varchar(100)') as OrderDetail
FROM @xmldata.nodes('/Orders/Order') x(c)

--XMl File Location: "C:\Users\User\X\Example.xml")

Thanks in advance!

Answer 1

Try this https://msdn.microsoft.com/en-CA/library/ms191184.aspx

INSERT INTO T(XmlCol)  
SELECT * FROM OPENROWSET(  
BULK 'c:\SampleFolder\SampleData3.txt',  
SINGLE_BLOB) AS x;