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

- & gt; Set on Automarketment name varchar (200) table - b bid int pk - & gt; Auto increment AID Ent PK FK Number 1 Ent holographic decimal (16,2)

not set between a and b Relationship between 1: M . Therefore, my C # App Table-B data is sent to XML with the following structure.

& lt; Table & gt; & Lt; RECORD & gt; & Lt; BINID & gt; 23 & lt; / BINID & gt; & Lt; NUMBER1 & gt; 123 & lt; / NUMBER1 & gt; & Lt; Holographic & gt; 2345.12 & lt; / Holographic & gt; & Lt; / Record & gt; & Lt; RECORD & gt; & Lt; BINID & gt; 3 & lt; / BINID & gt; & Lt; NUMBER1 & gt; 346,233 & lt; / NUMBER1 & gt; & Lt; Holographic & gt; 12.345 & lt; / Holographic & gt; & Lt; / Record & gt; & Lt; / TABLEB & gt; << Code>

In my SQL , first I insert name to table-A and then Table B . To get AID , first put me in the Table-A . Is there a way to record everyone at a time with xml with AID ? {Because xml does not have an ID value}

< P> You can create a stored procedure to do this. I do not know how you enter a name in the first table, but all the records can be included here:

  enter the process as some name @ narcharchar (max), @xmlData Declared as xml @aid int; - Insert data into the table in table A (name) values ​​(@ name); Choose @ed = scope_identity (); - The newly created identity value is received - Declare the cut XML data @douch.in; - Create document handle - Get XML data @x to XML = @ XMLDTD declare; - Prepare XML DOC ECC [SIS] Enter [SP_XMLPRR], @ DOCHOut, in table format, add @ xgate XML data and tableb (bid, support, number, holographic) to your destination table. , With no.1, OpenXML (@doc, '/ table / record', 3) (bINID int, no1 intel, holographic decimal (16,2) EXEC [sys]. [Sp_xml_removed document] @docH end  

Then, run it from the bottom:

  DECLARE @z AS XML = '& lt; Table & gt; & lt; RECORD & gt; & lt; BINID> 23 & lt; / BINID & gt; & lt; NUMBER1 & gt; 123 & lt; / NUMBER1 & gt; Holographic & gt; 2345.12 / holographic & gt; & lt; / record & Gt; & lt; / TABLEB & gt; 'EXEC [dbo]. [InsertSomething] @name = N'name', - nvarchar (max) @xmlData = @z - xml