DECLARE

@strXML NVARCHAR(4000)

,@XML XML

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

DROP TABLE #Sub

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

DROP TABLE #Contact

CREATE TABLE #Sub (

ID INT IDENTITY(1,1) NOT NULL,

SubID INT NOT NULL,

SubName NVARCHAR(50) NULL,

SubAddress NVARCHAR(50) NULL,

PRIMARY KEY (ID,SubID))

CREATE TABLE #Contact (

ID INT IDENTITY(1,1) NOT NULL,

SubID INT NOT NULL,

ContactAddress1 NVARCHAR(50) NULL,

ContactAddress2 NVARCHAR(50) NULL,

PRIMARY KEY (ID,SubID))

SET @strXML = '

<Sub>

<ID>1</ID>

<Name>Saran</Name>

<Address>123 Usa</Address>

<Contact>

<Address1>4534 China</Address1>

<Address2>7674 India</Address2>

</Contact>

</Sub>

<Sub>

<ID>2</ID>

<Name>Rajesh</Name>

<Address>456 India</Address>

<Contact>

<Address1>321 Japan</Address1>

<Address2>987 Korea</Address2>

</Contact>

</Sub>

'

BEGIN TRY

SELECT @XML = CONVERT(XML,@strXML)

INSERT INTO #Sub

SELECT

x.value('(ID)[1]','int') AS SubID

,x.value('(Name)[1]','varchar(30)') AS SubName

,x.value('(Address)[1]','varchar(30)') AS SubAddress

FROM @XML.nodes('/Sub') n(x)

INSERT INTO #Contact

SELECT

x.value('(ID)[1]','int') AS SubID

,x.value('(Contact/Address1)[1]','varchar(30)') AS ContactAddress1

,x.value('(Contact/Address2)[1]','varchar(30)') AS ContactAddress2

FROM @XML.nodes('/Sub') n(x)

SELECT

s.SubID

,s.SubName

,s.SubAddress

,c.ContactAddress1

,c.ContactAddress2

FROM

#Sub s

INNER JOIN

#Contact c

ON s.SubID = c.SubID

ORDER BY

s.SubID

END TRY

BEGIN CATCH

PRINT 'INVALID XML!'

END CATCH

Output

SubIDSubNameSubAddressContactAddress1ContactAddress2

1Saran123 Usa4534 China7674 India

2Rajesh456 India321 Japan987 Korea