Insert XML Data into sql Server table

Declare @retValue1 varchar(50);
Declare @XmlStr XML;
SET @XmlStr='<Customers>
 <customer>
    <ID>111589</ID>
    <FirstName>name1</FirstName>
    <LastName>Lname1</LastName>
    <Company>ABC</Company>
  </customer>
  <customer>
    <ID>12345</ID>
    <FirstName>name2</FirstName>
    <LastName>Lname2</LastName>
    <Company>ABC</Company>
  </customer>
  <customer>
    <ID>14567</ID>
    <FirstName>name3</FirstName>
    <LastName>Lname3</LastName>
    <Company>DEF</Company>
  </customer>
</Customers>';

@retValue='Failed';
 
INSERT INTO  test_xmlinsert
SELECT
COALESCE([Table].[Column].value('ID[1]', 'int'),0) as 'ID',
[Table].[Column].value('FirstName [1]', 'varchar(50)') as ' FirstName ',
[Table].[Column].value(' LastName[1]', 'varchar(50)') as ' LastName',
[Table].[Column].value(' Company [1]', 'varchar(50)') as ' Company'
 FROM @XmlStr.nodes('/ Customers / customer') as Table
IF(@@ROWCOUNT > 0 )
  SET @retValue='SUCCESS';

You just need to include N in front of your XML string to make it unicode.

INSERT INTO testfiles
  (filename, filemeta) 
VALUES 
  ('test.mp3', N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>');

The problem is reader.read() will read only the root node and there is only one node, so it won't move to next node. You should be navigating to child nodes by using ReadToDescendant method. The below code might work for you. Check my comments in the code.

          using (XmlTextReader reader = new XmlTextReader("D:\\test.xml"))
                {
                    reader.ReadToFollowing("Person"); // it will read the first node
                    if (reader.ReadToDescendant("Name_details")) // it will read the first descendent of Person
                    {
                        do
                        {
                            SqlCommand insertCommand = new SqlCommand("spInsertimiListes", conn);

                            if (reader.IsStartElement())
                            {
                                first_name = " ";
                                middle_name = " ";
                                surname = " ";
                                gender = " ";
                                occ_title = " ";

                                foreach (var item in reader.Name)
                                {
                                    if (reader.Name == "FirstName")
                                    {
                                        first_name = reader.ReadString();
                                    }
                                    else if (reader.Name == "MiddleName")
                                    {
                                        middle_name = reader.ReadString();
                                    }
                                    else if (reader.Name == "Surname")
                                    {
                                        surname = reader.ReadString();
                                    }
                                    else if (reader.Name == "Gender")
                                    {
                                        gender = reader.ReadString();
                                    }
                                    else if (reader.Name == "OccTitle")
                                    {
                                        occ_title = reader.ReadString();
                                    }
                                }
                            }

                            insertCommand.CommandType = CommandType.StoredProcedure;

                            insertCommand.Parameters.AddWithValue("FirstName", first_name);
                            insertCommand.Parameters.AddWithValue("MiddleName", middle_name);
                            insertCommand.Parameters.AddWithValue("Surname", surname);
                            insertCommand.Parameters.AddWithValue("Gender", gender);
                            insertCommand.Parameters.AddWithValue("OccTitle", occ_title);

                            if (!((first_name == " " & surname == " " & middle_name == " " & gender == " " & occ_title == " ")))
                            {
                                insertCommand.ExecuteNonQuery();
                            }
                        } while (reader.ReadToNextSibling("Name_details")); // it will read next descendent of person
                    }
                }

First of all: Your XML is not well-formed as it is missing a root node. SQL-server can deal with XML-fragments, but other engines might fail...

You have a 1:n related two level hierarchy. This is best queried with a cascade of .nodes():

DECLARE @XML AS XML= 
N'<Entity>
    <name>John</name>
    <aliases>
    <alias>Johnny</alias>
    </aliases>
    <aliases>
    <alias>Johnson</alias>
    </aliases>
</Entity>
<Entity>
    <name>Smith</name>
    <aliases>
    <alias>Smithy</alias>
    </aliases>
    <aliases>
    <alias>Schmit</alias>
    </aliases>
</Entity>' 

--the query will use .nodes() to get all entities and a second time .nodes() to get a derived table of the aliases below each entity.

SELECT  A.ent.value('(name/text())[1]','varchar(100)') As FirstName
       ,B.ali.value('(alias/text())[1]','varchar(50)') as SecondName
FROM @xml.nodes('/Entity') A(ent)
CROSS APPLY A.ent.nodes(N'aliases') AS B(ali);

UPDATE added attributes

According to one comment below I changed this a bit to reflect multiple aliases with an attribute.

DECLARE @XML AS XML= 
N'<Entity>
    <name>John</name>
    <aliases>
    <alias nr="1">Johnny</alias>
    <alias nr="2">OneMore</alias>
    </aliases>
    <aliases>
    <alias>Johnson</alias>
    </aliases>
</Entity>
<Entity>
    <name>Smith</name>
    <aliases>
    <alias nr="1">Smithy</alias>
    </aliases>
    <aliases>
    <alias nr="1">Schmit</alias>
    </aliases>
</Entity>' 

SELECT  A.ent.value('(name/text())[1]','varchar(100)') As FirstName
       ,B.ali.value('@nr','int') as SomeAttribute
       ,B.ali.value('text()[1]','varchar(50)') as SecondName
FROM @xml.nodes('/Entity') A(ent)
CROSS APPLY A.ent.nodes(N'aliases/alias') AS B(ali);

What has changed?

• .nodes() is diving one level deeper to /alias, thus repeating multiple aliases in multiple rows.
• reading the attribute with a leading @