• As you inferred, I needed to use dynamic SQL to get the results you want. From your example I wasn't able to get the XML nodes and your tables to match up exactly so I took the liberty of renaming a few items. You said this was just a small sample anyway so it should work for you once you match everything up with the real data and columns. I tried not to hardcode any more than necessary. I'm sure with some work you could probably replace the hardcoded values with variables easily enough.

    So we start with a (very slightly) modified version of your XML.

    EXEC dbo.ParseXMLNodeToStaging

    '<Root>

    <Application>

    <ID>1</ID>

    <ApplicantEntryComplete>false</ApplicantEntryComplete>

    <AssistantUnderwriter>davisg</AssistantUnderwriter>

    <Brokerage>Gamble Underwriters

    <AddressLine1>5605 Glenridge Dr NE</AddressLine1>

    <AddressLine2>One Premier Plaza, Ste 300</AddressLine2>

    <Contact>

    <ContactName>Jeremy Irons</ContactName>

    <PhoneNumber>555-123-4567</PhoneNumber>

    <Mobile>555-456-7890</Mobile>

    </Contact>

    </Brokerage>

    <InsuredCompany>

    <CompanyName>USA Corp</CompanyName>

    <AddressLine1>1100 Pennsylvania Ave</AddressLine1>

    <AddressLine2>White House Plaza, West Wing</AddressLine2>

    <Assets>0</Assets>

    <BusinessDesc>Government</BusinessDesc>

    <Contact>

    <ContactName>Melinda Bates</ContactName>

    <PhoneNumber>123-456-7890</PhoneNumber>

    <Mobile>123-456-7899</Mobile>

    </Contact>

    </InsuredCompany>

    </Application>

    <Application>

    <ID>2</ID>

    <ApplicantEntryComplete>false</ApplicantEntryComplete>

    <AssistantUnderwriter>johnd</AssistantUnderwriter>

    <Brokerage>Amelia Brothers

    <AddressLine1>1105 Independence Blvd</AddressLine1>

    <AddressLine2></AddressLine2>

    <Contact>

    <ContactName>Mary Johnson</ContactName>

    <PhoneNumber>704-678-1345</PhoneNumber>

    <Mobile>704-789-0909</Mobile>

    </Contact>

    </Brokerage>

    <InsuredCompany>

    <CompanyName>LittleTown Industries</CompanyName>

    <AddressLine1>123 Main St</AddressLine1>

    <AddressLine2>Apt 1</AddressLine2>

    <Assets>100</Assets>

    <BusinessDesc>IT</BusinessDesc>

    <Contact>

    <ContactName>Terrence OBrien</ContactName>

    <PhoneNumber>899-345-1234</PhoneNumber>

    <Mobile>899-640-9876</Mobile>

    </Contact>

    </InsuredCompany>

    </Application>

    </Root>'

    ,NULL

    ,'/Root'

    Before executing the procedure we must create the base tables (I used temp tables but you will of course use your own tables.

    /* These would be your permanent tables */

    IF OBJECT_ID('tempdb..#Stg_Application') IS NULL

    BEGIN

    Create table #Stg_Application

    (

    Appl_ID Int Identity(100,1) NOT NULL,

    ApplicantEntryCompleteVarchar(50),

    AssistantUnderwriterVarchar(50),

    PRIMARY KEY (Appl_ID)

    )

    END

    IF OBJECT_ID('tempdb..#Stg_Company') IS NULL

    BEGIN

    Create table #Stg_Company

    (

    Company_ID Int Identity(1000,10) NOT NULL,

    Appl_ID Int,

    BrokerageName Varchar(50),

    BrokerageAddressLine1Varchar(50),

    BrokerageAddressLine2Varchar(50),

    BrokerageContactName Varchar(50),

    BrokeragePhoneNumberVarchar(50),

    BrokerageMobile Varchar(50),

    CompanyName Varchar(50),

    Assets Int,

    BusinessDesc Varchar(50),

    AddressLine1 Varchar(50),

    AddressLine2 Varchar(50),

    PRIMARY KEY (Company_ID)

    )

    END

    IF OBJECT_ID('tempdb..#Stg_Contact') IS NULL

    BEGIN

    Create table #Stg_Contact

    (

    Cont_ID Int Identity(10000,10) NOT NULL,

    Company_ID Int,

    ContactName Varchar(50),

    PhoneNumberVarchar(50),

    Mobile Varchar(50),

    PRIMARY KEY (Cont_ID)

    )

    END

    --for cleanup while testing

    --drop table #Stg_Application

    --drop table #Stg_Company

    --drop table #Stg_Contact

    Finally, here's the code for a procedure to insert the data into the proper tables.

    CREATE PROCEDURE [dbo].[ParseXMLNodeToStaging]

    @strXML NVARCHAR(MAX)

    ,@schemanode NVARCHAR(255)

    ,@rootnode NVARCHAR(255)

    AS

    BEGIN

    SET NOCOUNT ON

    IF OBJECT_ID('tempdb..#NodeToStaging') IS NOT NULL

    DROP TABLE #NodeToStaging

    Create table #NodeToStaging

    (

    ID Int Identity(1,1) NOT NULL,

    Node Varchar(500),

    StagingTable Varchar(500),

    PRIMARY KEY (ID)

    )

    Insert into #NodeToStaging(Node,StagingTable)

    values

    ('Application','#Stg_Application'),

    ('Company','#Stg_Company'),

    ('Contact','#Stg_Contact')

    /* Another table to hold the XML results */

    IF OBJECT_ID('tempdb..#XML_Output') IS NOT NULL

    DROP TABLE #XML_Output

    Create table #XML_Output

    (

    ID Int Identity(1,1),

    RowNum int,

    nodeid int,

    nodepath nvarchar(255),

    nodename nvarchar(255),

    nodecontents nvarchar(255),

    PRIMARY KEY (ID,RowNum)

    )

    /* A variable to hold the dynamic insert statements */

    DECLARE @strSQL NVARCHAR(MAX)

    SET @strSQL = ''

    /* Parse the XML */

    INSERT INTO #XML_Output

    EXEC dbo.ParseXML

    @strXML

    ,@schemanode

    ,@rootnode

    /* Query the XML output and organize it */

    ;WITH

    cteApplication

    AS

    (

    SELECT

    x.ID

    ,ROW_NUMBER() OVER (PARTITION BY nodepath ORDER BY x.ID) AS Appl_ID

    ,1 AS nodetype

    ,'Application' AS nodedesc

    ,ROW_NUMBER() OVER (PARTITION BY nodename ORDER BY x.ID) AS nodeid

    ,nodename

    ,nodecontents

    ,s.StagingTable

    FROM

    #XML_Output x

    INNER JOIN

    #NodeToStaging s

    ON s.Node = 'Application'

    WHERE

    nodename IN ('ApplicantEntryComplete','AssistantUnderwriter')

    ),

    cteCompany

    AS

    (

    SELECT

    x.ID

    ,ROW_NUMBER() OVER (PARTITION BY nodepath ORDER BY x.ID) AS Appl_ID

    ,2 AS nodetype

    ,'Company' AS nodedesc

    ,ROW_NUMBER() OVER (PARTITION BY nodename ORDER BY x.ID) AS nodeid

    ,(CASE

    WHEN nodename = 'Brokerage' THEN 'BrokerageName'

    WHEN nodepath LIKE '/Application/Brokerage%' THEN 'Brokerage'+nodename

    ELSE nodename

    END) AS nodename

    ,nodecontents

    ,s.StagingTable

    FROM

    #XML_Output x

    INNER JOIN

    #NodeToStaging s

    ON s.Node = 'Company'

    WHERE

    nodepath LIKE '%Application/%'

    AND nodename NOT IN ('ApplicantEntryComplete','AssistantUnderwriter')

    AND nodepath NOT LIKE '%InsuredCompany/Contact%'

    ),

    cteContact

    AS

    (

    SELECT

    x.ID

    ,ROW_NUMBER() OVER (PARTITION BY nodepath ORDER BY x.ID) AS Appl_ID

    ,3 AS nodetype

    ,'Contact' AS nodedesc

    ,ROW_NUMBER() OVER (PARTITION BY nodename ORDER BY x.ID) AS nodeid

    ,nodename

    ,nodecontents

    ,s.StagingTable

    FROM

    #XML_Output x

    INNER JOIN

    #NodeToStaging s

    ON s.Node = 'Contact'

    WHERE

    nodepath LIKE '%/InsuredCompany/Contact%'

    AND nodename IN ('ContactName','PhoneNumber','Mobile')

    )

    SELECT

    @strSQL = @strSQL +

    'INSERT INTO '+r.StagingTable+' ('+

    (CASE nodedesc

    WHEN 'Application' THEN

    STUFF((SELECT ',['+nodename+']' AS [text()]

    FROM cteApplication

    WHERE

    nodetype = r.nodetype

    AND Appl_ID = r.Appl_ID

    AND nodename <> 'ID'

    AND NULLIF(nodecontents,'') IS NOT NULL

    ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID)

    FOR XML PATH('')

    ),1,1,'')

    WHEN 'Company' THEN

    '[Appl_ID],'+

    STUFF((SELECT ',['+nodename+']' AS [text()]

    FROM cteCompany

    WHERE

    nodetype = r.nodetype

    AND Appl_ID = r.Appl_ID

    AND nodename <> 'ID'

    AND NULLIF(nodecontents,'') IS NOT NULL

    ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID)

    FOR XML PATH('')

    ),1,1,'')

    WHEN 'Contact' THEN

    '[Company_ID],'+

    STUFF((SELECT ',['+nodename+']' AS [text()]

    FROM cteContact

    WHERE

    nodetype = r.nodetype

    AND Appl_ID = r.Appl_ID

    AND nodename <> 'ID'

    AND NULLIF(nodecontents,'') IS NOT NULL

    ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID)

    FOR XML PATH('')

    ),1,1,'')

    END)

    +') VALUES ('+

    (CASE nodedesc

    WHEN 'Application' THEN

    STUFF((SELECT ','''+nodecontents+'''' AS [text()]

    FROM cteApplication

    WHERE

    nodetype = r.nodetype

    AND Appl_ID = r.Appl_ID

    AND nodename <> 'ID'

    AND NULLIF(nodecontents,'') IS NOT NULL

    ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID)

    FOR XML PATH('')

    ),1,1,'')

    WHEN 'Company' THEN

    'CAST((SELECT MAX(Appl_ID) FROM #Stg_Application) AS VARCHAR(10))'+','+

    STUFF((SELECT ','''+nodecontents+'''' AS [text()]

    FROM cteCompany

    WHERE

    nodetype = r.nodetype

    AND Appl_ID = r.Appl_ID

    AND nodename <> 'ID'

    AND NULLIF(nodecontents,'') IS NOT NULL

    ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID)

    FOR XML PATH('')

    ),1,1,'')

    WHEN 'Contact' THEN

    'CAST((SELECT MAX(Company_ID) FROM #Stg_Company) AS VARCHAR(10))'+','+

    STUFF((SELECT ','''+nodecontents+'''' AS [text()]

    FROM cteContact

    WHERE

    nodetype = r.nodetype

    AND Appl_ID = r.Appl_ID

    AND nodename <> 'ID'

    AND NULLIF(nodecontents,'') IS NOT NULL

    ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID)

    FOR XML PATH('')

    ),1,1,'')

    END)

    +');

    '

    FROM

    (

    SELECT

    Appl_ID

    ,nodetype

    ,ROW_NUMBER() OVER (PARTITION BY Appl_ID,nodedesc ORDER BY ID) AS typeorder

    ,nodedesc

    ,ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) AS nodeid

    ,nodename

    ,nodecontents

    ,StagingTable

    FROM

    cteApplication a

    UNION ALL

    SELECT

    Appl_ID

    ,nodetype

    ,ROW_NUMBER() OVER (PARTITION BY Appl_ID,nodedesc ORDER BY ID) AS typeorder

    ,nodedesc

    ,ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) AS nodeid

    ,nodename

    ,nodecontents

    ,StagingTable

    FROM

    cteCompany b

    UNION ALL

    SELECT

    Appl_ID

    ,nodetype

    ,ROW_NUMBER() OVER (PARTITION BY Appl_ID,nodedesc ORDER BY ID) AS typeorder

    ,nodedesc

    ,ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) AS nodeid

    ,nodename

    ,nodecontents

    ,StagingTable

    FROM

    cteContact c

    ) r

    WHERE

    typeorder = 1

    AND NULLIF(nodecontents,'') IS NOT NULL

    ORDER BY

    Appl_ID

    ,nodetype

    ,typeorder

    ,nodeid

    /* Execute the dynamic SQL */

    EXEC sp_executesql @strSQL

    /* See the results */

    SELECT * FROM #Stg_Application

    SELECT * FROM #Stg_Company

    SELECT * FROM #Stg_Contact

    END