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

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