XML Insert : Sql

DECLARE @XML AS XML = '<FileList>
       <FileInfo FileSrNo="0" FilePath="11" />
       <FileInfo FileSrNo="11" FilePath="1" />
       <FileInfo FileSrNo="2" FilePath="11" >
        <OtherInfo FileSrNo="222" FilePath="1122" >
         <FName>Ankit</FName>
         <LName>Somani</LName>
        </OtherInfo>
       </FileInfo>
      </FileList>'

SELECT FileInfo.value('@FileSrNo', 'INT') AS FileSrNo
 , FileInfo.value('@FilePath', 'VARCHAR(500)') AS FilePath
FROM @XML.nodes('/FileList/FileInfo')e(FileInfo)

SELECT FileInfo.value('@FileSrNo', 'INT') AS FileSrNo
 , FileInfo.value('@FilePath', 'VARCHAR(500)') AS FilePath
FROM @XML.nodes('/FileList/FileInfo/OtherInfo')e(FileInfo)

SELECT FileInfo.value('.', 'VARCHAR(25)') AS FName
FROM @XML.nodes('/FileList/FileInfo/OtherInfo/FName')e(FileInfo)

SELECT FileInfo.value('.', 'VARCHAR(25)') AS LName
FROM @XML.nodes('/FileList/FileInfo/OtherInfo/LName')e(FileInfo)

SELECT FileInfo.value('@FileSrNo', 'INT') AS FileSrNo
 , FileInfo.value('@FilePath', 'VARCHAR(500)') AS FilePath
 , FName.value('.', 'VARCHAR(25)') AS FName
 , LName.value('.', 'VARCHAR(25)') AS LName
FROM @XML.nodes('/FileList/FileInfo/OtherInfo')e(FileInfo)
 CROSS APPLY FileInfo.nodes('FName')ef(FName)
 CROSS APPLY FileInfo.nodes('LName')el(LName)



Out Put :

FileSrNo  FilePath
0             11
11           1
2             11
-----------------------------
FileSrNo   FilePath
222           1122
-----------------------------
FName
Ankit
-----------------------------
LName
Somani
-----------------------------
FileSrNo    FilePath    FName    LName
222            1122        Ankit       Somani


=========================================

SP:
==

ALTER PROCEDURE [dbo].[spInfoSave]
(
    @AID    VARCHAR(12),
    @XML    XML
)
AS
BEGIN
    SET NOCOUNT ON
   
    MERGE tblTEST
    USING
    (
        SELECT LinkInfo.value('@LinkSrNo', 'TINYINT') AS LinkSrNo
             , LinkInfo.value('@LinkPath', 'VARCHAR(500)') AS LinkPath
        FROM @XML.nodes('/LinkList/LinkInfo')e(LinkInfo)
    ) AS tblSrc
        ON tblSrc.LinkSrNo = tblTEST.LinkSrNo
            AND tblTEST.AID = @AID
    WHEN MATCHED THEN
        UPDATE
        SET  LinkPath = tblSrc.LinkPath
           
    WHEN NOT MATCHED THEN
        INSERT (
                      AID
                    , LinkSrNo
                    , LinkPath
                )
        VALUES (
                      @AID
                    , tblSrc.LinkSrNo
                    , tblSrc.LinkPath
                )
    WHEN NOT MATCHED BY SOURCE AND tblTEST.AID = @AID
        THEN DELETE;
END