Creating XML from SQL Table (Unique XML formatting)

But the issue at hand is that the XML styling/formatting is quite odd...

What is odd there? The only thing odd I can see is the attempt to solve this on string level...

Your question is missing sample data and expected output. The simple select you provide tells us nothing, the XML you provide is an inclompete fragment and the actual example is something completely different...

Just some hints:

• Do not concatenate XML on string level!
• please read How to ask a good SQL question
• and How to create a MCVE

Your simple example should be done like this:

DECLARE @NAME varchar(50)
DECLARE @LOCATION varchar(50)
DECLARE @SearchXML xml

SET @SearchXML = 
(
    SELECT @LOCATION AS LastName
          ,@NAME AS FirstName
    FOR XML PATH('CallerInformation'),ROOT('Root'),TYPE

);

SELECT @SearchXML;

This will lead to an almost empty (but valid!) XML, put any value into the variables and you will see the XML filled.

UPDATE: Your odd XML...

Try something like this:

SET @xml=
(
    SELECT '170607A13' AS ID
          ,'170607A13' AS [MaterialActual/MaterialLot]
          ,'CreationDate' AS [MaterialActual/MaterialActualProperty/ID]
          ,GETDATE() AS [MaterialActual/MaterialActualProperty/Value/ValueString]
    FOR XML PATH('')
);
SELECT @xml

UPDATE 2: Very long XPath...

This is your error: name-length more then 128

DECLARE @xml XML;
--SET @xml=
--(
--    SELECT '170607A13' AS ID
--          ,'170607A13' AS [MaterialActual1234567890/MaterialLot1234567890]
--          ,'CreationDate' AS [MaterialActual1234567890/SomeMore1234567890/EvenMore1234567890/StillMore1234567890/MaterialActualProperty1234567890/ID1234567890]
--          ,GETDATE() AS [MaterialActual1234567890/SomeMore1234567890/EvenMore1234567890/StillMore1234567890/MaterialActualProperty1234567890/ValueString1234567890]
--    FOR XML PATH('')
--);
--SELECT @xml

--This is a solution: nested sub-select:

SET @xml=
(
    SELECT '170607A13' AS ID
          ,'170607A13' AS [MaterialActual1234567890/MaterialLot1234567890]
          ,(
          SELECT
          'CreationDate' AS [EvenMore1234567890/StillMore1234567890/MaterialActualProperty1234567890/ID1234567890]
          ,GETDATE() AS [EvenMore1234567890/StillMore1234567890/MaterialActualProperty1234567890/ValueString1234567890]
          FOR XML PATH('SomeMore1234567890'),TYPE
          ) AS [MaterialActual1234567890]
    FOR XML PATH('')
);
SELECT @xml;

UPDATE 3: Your follow-up question in comment

HINT: Avoid follow-up questions. Next time please add a new question!

Both return the result requested:

SELECT 'yyyy-MM-dd''T''HH:mm:ss.SSSXXX' AS [PublishedDate/@format]
      ,GETDATE() AS PublishedDate
FOR XML PATH('')

SELECT 'yyyy-MM-dd''T''HH:mm:ss.SSSXXX' AS [@format]
      ,GETDATE() AS [*]
FOR XML PATH('PublishedDate');

In my eyes there's no need for the format. Within XML a datetime should be in this format (which is ISO8601) anyway. This is the standard format...