Binaryworld - How to load XML data in sql server table using stored procedure ... [ SQL Server -> XML ]

This article will show you a simple example to load XML document in SQL Server

Create PROCEDURE sp_LoadXML
@strXML ntext
AS
  DECLARE @iDoc int

  EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

     Create table #t1(Column1 VARCHAR(100),Column2 VARCHAR(100))

   INSERT INTO #t1(Column1,Column2)
   SELECT * FROM OpenXML(@iDoc, '/MyTable/Row', 2)
   WITH (Column1 VARCHAR(100),Column2 VARCHAR(100))

   EXECUTE sp_xml_removedocument @iDoc
select * from #t1
GO

exec sp_LoadXML '<MyTable>
<Row>
<Column1>aaaa1</Column1>
<Column2>bbbb1</Column2>
</Row>
<Row>
<Column1>aaaa2</Column1>
<Column2>bbbb2</Column2>
</Row>
</MyTable>'

-- Or

--//With XML as a root tag
--//<?xml version="1.0"?> tag is optional

exec sp_LoadXML '<?xml version="1.0"?>
<MyTable>
<Row>
<Column1>aaaa1</Column1>
<Column2>bbbb1</Column2>
</Row>
<Row>
<Column1>aaaa2</Column1>
<Column2>bbbb2</Column2>
</Row>
</MyTable>'