Try this procedure. You may have to make some modifications to fit your XML structure. And what you do with the output is up to you.
CREATE PROCEDURE dbo.ParseXML
@strXML NVARCHAR(MAX)
,@schemanode NVARCHAR(255)
,@rootnode NVARCHAR(255)
AS
BEGIN
SET NOCOUNT ON
DECLARE
@idoc INT
,@id INT
,@cpid INT
,@SoapEnvOpen NVARCHAR(MAX)
,@SoapEnvClose NVARCHAR(MAX)
,@SoapBodyOpen NVARCHAR(MAX)
,@SoapBodyClose NVARCHAR(MAX)
,@SchemaNodeOpen NVARCHAR(MAX)
,@SchemaNodeClose NVARCHAR(MAX)
,@checkSoap INT
,@isSoap BIT = 0
,@checkSchema INT
,@isSchema BIT = 0
,@checkXMLHeader INT
,@isHeader BIT = 0
,@XMLHeader NVARCHAR(MAX)
,@debug BIT = 0
IF OBJECT_ID('tempdb..#TreeList') IS NOT NULL
DROP TABLE #TreeList
IF OBJECT_ID('tempdb..#NodeList') IS NOT NULL
DROP TABLE #NodeList
IF OBJECT_ID('tempdb..#OutputList') IS NOT NULL
DROP TABLE #OutputList
SET @id = 1
SET @cpid = NULL
/* Check to see if any XML exists */
SET @strXML = NULLIF(@strXML,'')
IF @strXML IS NULL
BEGIN
SELECT
NULL AS RowNum
,NULL AS id
,NULL AS parentid
,NULL AS nodepath
,NULL AS nodetype
,NULL AS nodename
,NULL AS property
,NULL AS value
,NULL AS nodecontents
,'No XML to process' AS XMLStatus
RETURN
END
ELSE
BEGIN
-- Get rid of tabs and extra spaces
SET @strXML = REPLACE(@strXML,CHAR(9),'')
SET @strXML = REPLACE(@strXML,CHAR(10),'')
SET @strXML = REPLACE(@strXML,CHAR(13),'')
IF CHARINDEX('<',@strXML,1) > 0
BEGIN
SET @strXML = REPLACE(@strXML,'<','<')
SET @strXML = REPLACE(@strXML,'>','>')
IF @debug = 1
SELECT @strXML AS ReplacedXML
END
SET @strXML =
REPLACE(
REPLACE(
REPLACE(
REPLACE(
@strXML
,' ',' '+CHAR(7))
,CHAR(7)+' ','')
,CHAR(7),'')
,'> <','><')
END
IF @debug = 1
SELECT @strXML AS InputXML
/* Check to see if the XML has a header */
SET @checkXMLHeader = CHARINDEX('<?xml version',@strXML,0)
IF @checkXMLHeader > 0
SET @isHeader = 1
/* If the XML has a header then remove it */
IF @isHeader = 1
BEGIN
SET @XMLHeader = SUBSTRING(@strXML,CHARINDEX('<?xml version',@strXML,0),CHARINDEX('>',@strXML,0))
SET @strXML = REPLACE(@strXML,@XMLHeader,'')
END
IF @debug = 1
SELECT @XMLHeader AS XMLHeader
/* Check to see if the XML has a SOAP wrapper */
SET @checkSoap = CHARINDEX('<soapenv:Envelope',@strXML,0)
IF @checkSoap > 0
SET @isSoap = 1
/* If the XML has a SOAP wrapper then remove it */
IF @isSoap = 1
BEGIN
SET @SoapEnvOpen = SUBSTRING(@strXML,CHARINDEX('<soapenv:Envelope',@strXML,0),CHARINDEX('>',@strXML,0))
SET @strXML = REPLACE(@strXML,@SoapEnvOpen,'')
SET @SoapBodyOpen = SUBSTRING(@strXML,CHARINDEX('<soapenv:Body',@strXML,0),CHARINDEX('>',@strXML,0))
SET @strXML = REPLACE(@strXML,@SoapBodyOpen,'')
SET @SoapEnvClose = SUBSTRING(@strXML,CHARINDEX('</soapenv:Envelope>',@strXML,0),LEN('</soapenv:Envelope>'))
SET @strXML = REPLACE(@strXML,@SoapEnvClose,'')
SET @SoapBodyClose = SUBSTRING(@strXML,CHARINDEX('</soapenv:Body>',@strXML,0),LEN('</soapenv:Body>'))
SET @strXML = REPLACE(@strXML,@SoapBodyClose,'')
END
IF @debug = 1
BEGIN
SELECT @SoapEnvOpen AS Soap_Wrapper
SELECT @SoapBodyOpen AS Soap_Body
SELECT @strXML AS XML_Without_SoapWrapper
END
/* Check to see if the XML has a schema definition node */
SET @checkSchema = CHARINDEX('<'+@schemanode,@strXML,0)
IF @checkSchema > 0
SET @isSchema = 1
/* If a schema definition node exists remove it */
IF @isSchema = 1
BEGIN
SET @SchemaNodeOpen = SUBSTRING(@strXML,CHARINDEX('<'+@schemanode,@strXML,0),CHARINDEX('>',@strXML,0))
SET @strXML = REPLACE(@strXML,@SchemaNodeOpen,'')
SET @SchemaNodeClose = SUBSTRING(@strXML,CHARINDEX('</'+@schemanode+'>',@strXML,0),LEN('</'+@schemanode+'>'))
SET @strXML = REPLACE(@strXML,@SchemaNodeClose,'')
END
BEGIN TRY
EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML
END TRY
BEGIN CATCH
BEGIN
IF @debug = 1
BEGIN
SELECT @strXML AS ModifiedXML
SELECT CONVERT(XML,@strXML) AS FormattedXML
END
SELECT
NULL AS RowNum
,NULL AS id
,NULL AS parentid
,NULL AS nodepath
,NULL AS nodetype
,NULL AS nodename
,NULL AS property
,NULL AS value
,NULL AS nodecontents
,'Invalid XML' AS XMLStatus
RETURN
END
END CATCH
;WITH TreeList (cpid,cid)
AS
(
SELECT
CAST(p1.parentid AS INT) AS cpid
,CAST(p1.id AS INT) AS cid
FROM
OPENXML (@idoc,@rootnode,2) AS p1
UNION ALL
SELECT
CAST(p2.parentid AS INT) AS cpid
,CAST(p2.id AS INT) AS cid
FROM
OPENXML (@idoc,@rootnode,2) AS p2
JOIN
TreeList
ON CAST(TreeList.cid AS INT) = CAST(p2.parentid AS INT)
WHERE
CAST(p2.parentid AS INT) = @cpid
),
NodeList (nid,npid,nodetype,localname,[text])
AS
(
SELECT
xmllist.id AS nid
,xmllist.parentid AS npid
,xmllist.nodetype
,xmllist.localname
,CAST(xmllist.[text] AS NVARCHAR(MAX)) AS [text]
FROM TreeList
INNER JOIN
OPENXML (@idoc,@rootnode,2) AS xmllist
ON TreeList.cid = xmllist.id
)
SELECT
IDENTITY(INT,1,1) AS tRow
,t.cid
,t.cpid
,n.nid
,n.npid
,n.nodetype
,n.localname
,n.[text]
INTO #TreeList
FROM
TreeList t
INNER JOIN
NodeList n
ON n.npid = t.cid
--SELECT
--*
--FROM
-- #TreeList
;WITH RecursiveNodes(tRow,id,parentid,nodepath,localname,[text],nodetype)
AS (
SELECT
#TreeList.tRow
,#TreeList.nid AS id
,#TreeList.npid AS parentid
,CAST('/' + REPLACE(REPLACE(REPLACE(REPLACE(#TreeList.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
,#TreeList.localname
,CAST(#TreeList.[text] AS NVARCHAR(MAX)) AS [text]
,0 AS nodetype
FROM
#TreeList
WHERE
#TreeList.cpid IS NULL
UNION ALL
SELECT
n.tRow
,n.nid AS id
,n.npid AS parentid
,CAST(r.nodepath + '/'+ REPLACE(REPLACE(REPLACE(REPLACE(n.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
,n.localname
,n.[text]
,n.nodetype
FROM #TreeList AS n
INNER JOIN
RecursiveNodes AS r
ON r.id = n.npid
)
SELECT
ROW_NUMBER() OVER (ORDER BY Result.id) AS RowNum
,ROW_NUMBER() OVER (PARTITION BY nodename ORDER BY Result.id) AS nodeid
,Result.nodepath
,Result.nodename
,Result.nodecontents
INTO #OutputList
FROM
(
SELECT
rn.tRow
,rn.id
,rn.parentid
,rn.nodepath
,(CASE
WHEN rn.nodetype = 0 THEN 'Root'
WHEN rn.nodetype = 1 THEN 'Node'
WHEN rn.nodetype = 2 THEN 'Property'
ELSE 'Data'
END) AS nodetype
,(CASE
WHEN rn.nodetype = 0 THEN rn.localname
WHEN rn.nodetype = 1 THEN rn.localname
WHEN rn.nodetype = 2 THEN (SELECT TOP(1) localname FROM RecursiveNodes WHERE id = rn.parentid)
ELSE NULL
END) AS nodename
,ISNULL((CASE
WHEN rn.nodetype = 1 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.parentid and [text] is not null)
ELSE NULL
END),'') AS nodecontents
FROM
RecursiveNodes AS rn
) AS Result
WHERE
nodetype <> 'Root'
AND nodename IS NOT NULL
ORDER BY
nodeid
,RowNum
SELECT * FROM #OutputList
END
Now run it with your sample data. Notice I added a telephone number node that is commented out.
Remove the comments and see that the nodes are parsed dynamically.
EXEC dbo.ParseXML
'<Root>
<Sub>
<ID>1</ID>
<Name>Saran</Name>
<!--Telephone>123-456-7890</Telephone-->
<Address>123 Usa</Address>
<Contact>
<Address1>4534 China</Address1>
<Address2>7674 India</Address2>
</Contact>
</Sub>
<Sub>
<ID>2</ID>
<Name>Rajesh</Name>
<!--Telephone>987-654-3210</Telephone-->
<Address>456 India</Address>
<Contact>
<Address1>321 Japan</Address1>
<Address2>987 Korea</Address2>
</Contact>
</Sub>
</Root>'
,NULL
,'/Root'