insert into a table by openXml and variable

I have a table that should be filled by a stored procedure. Some of its columns are filled by xml data file and a column (ExGroup ) by a value.

I’ve used OpenXml method for it but I don’t know how to define data of Exgroup by @exgroup value.

My query is this:

 

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

 

 

 

ALTER   PROCEDURE [dbo].[sp0]

(

            @strXML ntext,@exgroup int

)

AS

Declare @intPointer int

exec sp_xml_preparedocument @intPointer output, @strXML

Insert into Symbols_Stage (symbol,longname, shortname,Exchange,ExGroup) Select * from OpenXml(@intPointer,'/results/lookupdata/key',2)

      With (symbol char(20) 'symbol', longname varchar(100) '../equityinfo/longname', shortname varchar(100) '../equityinfo/shortname',Exchange char(20) 'exchange',ExGroup int )

exec sp_xml_removedocument @intPointer

RETURN

 




Answer this question

insert into a table by openXml and variable

  • Jarod.Net

    Hello Mark,

    Thanks a lot for your usefull help. now my program works fine by your guidance.

    Best wishes and good luck.



  • CodeSweatAndBeers

    Is this what you mean


    Insert into Symbols_Stage (symbol,longname, shortname,Exchange,ExGroup)
    Select symbol,longname, shortname,Exchange,@exgroup
    from OpenXml(@intPointer,'/results/lookupdata/key',2)
    With (symbol char(20) 'symbol',
    longname varchar(100) '../equityinfo/longname',
    shortname varchar(100) '../equityinfo/shortname',
    Exchange char(20) 'exchange')


  • insert into a table by openXml and variable