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