Insert Into Two SQL Tables From XML Maintaining Relationship
- by Thx
I am looking to insert records from xml into two different tables.
For example
<Root>
<A>
<AValue>1</AValue>
<Children>
<B>
<BValue>2</BValue>
</B>
</Children>
</A>
</Root>
Would insert a record into table A
AID AValue
# 1
also insert a record into table B
BID AID BValue
# #(Same as AID Above) 2
I have this
DECLARE @idoc INT
DECLARE @doc NVARCHAR(MAX)
SET @doc = '
<Root>
<A>
<AValue>1</AValue>
<Children>
<B>
<BValue>2</BValue>
</B>
</Children>
</A>
</Root>
'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
CREATE TABLE #A
(
AID INT IDENTITY(1, 1) ,
AValue INT
)
INSERT INTO #A
SELECT *
FROM OPENXML (@idoc, '/Root/A',2)
WITH (AValue INT
)
CREATE TABLE #B
(
BID INT IDENTITY(1, 1) ,
AID INT ,
BValue INT
)
INSERT INTO #B
SELECT *
FROM OPENXML (@idoc, '/Root/A/Children/B',2)
WITH (
AID INT,
BValue INT
)
SELECT *
FROM #A
SELECT *
FROM #B
DROP TABLE #A
DROP TABLE #B
Thanks!
