views:

111

answers:

1

Iam having one SP for which the input is a XML document . I want to insert the values from the XML into a temp table . How i can implement this one ...?

+2  A: 
CREATE TABLE [dbo].[myTable]
    (
      [a] [int] NULL,
      [b] [varchar](50) NULL,
      [c] [datetime] NULL
    ) ;

DECLARE @p_xml XML

SET @p_xml = '<root>
      <table a="123">
       <b>ABC</b>
       <c>2009-10-20</c>
      </table>
     </root>'

INSERT  INTO myTable
        ( a, b, c)
        SELECT
            x.mytable.value('@a[1]', 'INT'),
            x.mytable.value('b[1]', 'VARCHAR'),
            x.mytable.value('c[1]', 'DATETIME')
        FROM
            @p_xml.nodes('//root/table') AS x ( mytable )
Mevdiven