c# - How to insert an XML collection to table from a single insert statement? -

i have table structure in sql server

table-a aid    int (pk)--> set autoincrement name varchar(200)  table - b bid  int pk  --> not set auto increment aid  int pk fk number1   int holographic  decimal(16,2) 

the relationship between a , b 1 : m. c# app table-b data send xml following structure.

<tableb> <record>   <binid>23</binid>   <number1>123</number1>   <holographic>2345.12</holographic> </record> <record>   <binid>3</binid>   <number1>346233</number1>   <holographic>12.345</holographic> </record> </tableb> 

in sql, first insert name record table-a , insert table-b. obtain aid must first insert table-a. there way save xml records @ once aid value? { because xml not have aid value in it}

you can create stored procedure that. don't know how insert name first table, here's how sp can insert records:

create procedure insertsomething @name nvarchar(max), @xmldata xml begin declare @aid int; -- insert data table insert tablea (name) values(@name); select @aid = scope_identity(); -- gets newly created identity value -- shred xml data declare @doch int; -- create document handle -- xml data declare @x xml = @xmldata; -- prepare xml doc exec [sys].[sp_xml_preparedocument] @doch output, @x --get xml data in table format , insert destination table insert tableb (bid, aid, number, holographic) select binid, @aid, number1, holographic    openxml(@doch, '/tableb/record', 3)  with(binid int, number1 int, holographic decimal(16,2)) exec [sys].[sp_xml_removedocument] @doch end 

then, run below:

declare @z xml = '<tableb> <record> <binid>23</binid> <number1>123</number1> <holographic>2345.12</holographic> </record></tableb>'  exec [dbo].[insertsomething] @name = n'name', -- nvarchar(max) @xmldata = @z -- xml