From my C# code i execute following SP, to save the xml file into a table.
I would like to save a row in my table for each ComponentModule element.
Now i have something like this in my db table:
ID ComponentNo Lenght ComponentModule ID
1 2 3 5
What i want is:
ID ComponentNo Lenght ComponentModule ID
1 2 3 5
1 2 3 6
My SP to save xml data in Components table:
XML structure is something like this:
Thanks in advance!
I would like to save a row in my table for each ComponentModule element.
Code:
<ComponentModules>
<ComponentModule ComponentModuleID="5"/>
<ComponentModule ComponentModuleID="6"/>
</ComponentModules>
ID ComponentNo Lenght ComponentModule ID
1 2 3 5
What i want is:
ID ComponentNo Lenght ComponentModule ID
1 2 3 5
1 2 3 6
My SP to save xml data in Components table:
Code:
CREATE PROCEDURE [dbo].[insert_Components_XML]
@XMLproductDoc xml
AS
BEGIN
SET NOCOUNT ON;
insert into Components (ID,ComponentNo,Length,ComponentModule)
SELECT x.y.value('@ID','varchar(30)'),
x.y.value('@ComponentNo','varchar(30)'),
x.y.value('@Length','varchar(30)'),
x.y.value('(./ComponentModules//ComponentModule//@ComponentModuleID [1]','varchar(30)')
FROM @XMLproductDoc.nodes('/Product/ProductDetails/Components/Component') AS x(y)
END
Code:
<Product atr1="1" atr2="2" atr3="3">
<ProductDetails atribute="1" atribute2="2">
<someother>
</someother>
<Components ID="name">
<Component ID="1" ComponentNo="2" Length="3">
<ComponentModules>
<ComponentModule ComponentModuleID="5"/>
<ComponentModule ComponentModuleID="6"/>
</ComponentModules>
</Component>
</Components>
</ProductDetails>
</Product>