insert into a table by openXml and variable

Category: sql server xml

Question

Moridi on Mon, 17 Jul 2006 08:36:41


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

 

Replies

Mark - SQL on Mon, 17 Jul 2006 13:39:47


 

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')

 

Moridi on Wed, 19 Jul 2006 07:51:05


Hello Mark,

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

Best wishes and good luck.

GowriRamesh on Tue, 25 Mar 2014 10:19:20


Thanku Its Very Helpful..