Best way to generate XML using TSQL

The code below is wrong on many levels, I cannot list them. I assume that anyone trying to answer this question will use a test data table. Ha!

I've put together a few posts jumping between EXPLICIT, RAW and PATH and this has improved a bit.

I think someone who uses SQL to generate XML often knows this head first.

I have no preference for Ex, Raw or Path - I only need the best tool for the job. At the end of the day, there will be 65 columns making up the various levels of the final document.

I need some code to query this table and create this:

<batchContactList>
    <contact contactID="123" action="AddOrModify">
        <contactField name="FirstName">Johnny</contactField>
        <contactField name="LastName">Testguy</contactField>
        <contactPointList>
            <contactPoint type="Email">
            <contactPointField name="Label">Email</contactPointField>
            <contactPointField name="Address">example@sendwordnow.com</contactPointField>
                </contactPoint>
             </contactPointList>
    </contact>
</batchContactList>

      

        
        
        
      

    

Test code

- test data

DECLARE @tvTest TABLE (contactID INT, FirstName VARCHAR(25), LastName VARCHAR(25), [type] VARCHAR(25), [address] VARCHAR(25))
INSERT INTO @tvTest (contactID, FirstName, LastName, [type], [address])
SELECT 123, 'Johnny', 'Testguy', 'email', 'example@sendwordnow.com'
UNION
SELECT 321, 'Sally', 'Testgirl', 'email', '1example@sendwordnow.com';

--Outer
SELECT
      A.contactID AS "@contactID"
    , 'AddOrModify' AS "@action"
FROM 
    @tvTest A
FOR XML PATH('contact'), ROOT('batchContactList')

--Level 1
DECLARE @xmldata XML;
SELECT @xmldata = (SELECT contactID, FirstName, LastName FROM @tvTest FOR XML PATH (''));

SELECT
      ColumnName AS "@name"
    , ColumnValue AS "text()"
FROM
    (SELECT 
          i.value('local-name(.)','varchar(100)') ColumnName
        , i.value('.','varchar(100)') ColumnValue
    FROM 
        @xmldata.nodes('//*[text()]') x(i)) tmp
FOR XML PATH ('contactField'), ROOT('contact')

SELECT @xmldata = (SELECT contactID, [type], [address] FROM @tvTest FOR XML PATH (''));

--Level 2, not complete
SELECT
      ColumnName AS "@name"
    , ColumnValue AS "text()"
FROM
    (SELECT 
          i.value('local-name(.)','varchar(100)') ColumnName
        , i.value('.','varchar(100)') ColumnValue
    FROM 
        @xmldata.nodes('//*[text()]') x(i)) tmp
FOR XML PATH ('contactPoint'), ROOT('contactPointList')