Saturday, December 20, 2014

Insert xml data in sql server using OPENXML

DECLARE @xml NVARCHAR(max)

SET @xml ='<Delivery><item reqNo="REQ001" code="0001" Qty="25"/><item reqNo="REQ001" code="0002" Qty="35"/></Delivery>'


declare @xml_hndl int    
   
    exec sp_xml_preparedocument @xml_hndl OUTPUT, @xml


Select *

        From
            OPENXML(@xml_hndl, '/Delivery/item', 1)
            With
               (
                 reqNo NVARCHAR(20) '@reqNo',
                 code NVARCHAR(20) '@code',
                 qty money '@Qty'
               )
              

No comments:

Post a Comment