Import XML into SQL Server

The XML file (C:\SQL\some_xml.xml):

For this Type of XML:

note: C = C driver on the server.

SQL:

--  table to insert the data

CREATE TABLE #XML_Info (

   ColumnA varchar(100),

   ColumnB varchar(100), 

   ColumnInt numeric(5,2)

)

DECLARE @XMLData TABLE (XMLData XML)

INSERT INTO @XMLData

SELECT *

FROM OPENROWSET(BULK N'C:\SQL\some_xml.xml', SINGLE_BLOB) rs

select * from @XMLData

INSERT INTO #XML_Info (ColumnA, ColumnB, ColumnInt)

SELECT ColumnA = x.data.value('COLUMNA[1]','varchar(100)'),

       ColumnB = x.data.value('COLUMNB[1]','varchar(100)'),

       ColumnInt = x.data.value('COLUMNINT[1]','numeric(5,2)')

FROM @XMLData t

CROSS APPLY t.XMLData.nodes('/ROOTTAG/PARTAG') x(data)

SELECT * FROM #XML_Info

drop table #XML_Info

Result:

For this Type of XML:

<rows>
<row ColumnA="25759" ColumnB="171639" />
<row ColumnA="25762" ColumnB="171639" />
<row ColumnA="25763" ColumnB="171639" />
<row ColumnA="302665" ColumnB="152887" />

</rows>


DECLARE @MyTable TABLE 
( ColumnA int, ColumnB int)
DECLARE @XMLData1 TABLE (XMLData XML)
DECLARE @XML1 XML, @hDoc1 int  

INSERT INTO @XMLData1
SELECT * FROM OPENROWSET(BULK N'C:\SQL\some_xml.xml', SINGLE_BLOB) rs
SELECT @XML1 = XMLData from @XMLData1
--SELECT @XML1
exec sp_xml_preparedocument @hDoc1 output,@XML1

INSERT INTO @MyTable (ColumnA, ColumnB)
select *   
from OPENXML(@hDoc1,'/rows/row') with (  
ColumnA int '@ColumnA',   
ColumnB int '@ColumnB'
)  

exec sp_xml_removedocument @hDoc1