save multiple records from xml column to multiple rows in db table

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • sasha3
    New Member
    • Aug 2010
    • 6

    save multiple records from xml column to multiple rows in db table

    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.

    Code:
    <ComponentModules>
        <ComponentModule ComponentModuleID="5"/>
        <ComponentModule ComponentModuleID="6"/>
    </ComponentModules>
    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:

    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
    XML structure is something like this:

    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>
    Thanks in advance!
Working...