How do I insert values from an XML document into a temp table, in SQL Server?

Question

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 ...?

mevdiven · Accepted Answer · 2009-10-20 16:10:49Z

2

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 )

edited Oct 20, 2009 at 16:10

answered Oct 20, 2009 at 16:02

mevdiven

1,9022 gold badges17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I insert values from an XML document into a temp table, in SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related