SQL Server. Separate XML file with FOR XML PATH(...) into a single XML file

I've been reading this article: https://www.red-gate.com/simple-talk/sql/database-administration/manipulating-xml-data-in-sql-server/

I found what I need, but the only difference in my situation I need to use FOR XML PATH(...) and not ROOT(...) instead of AUTO mode.

I have a XML file that uses columns of the table as a elements. The question that I'm trying to resolve:

1) how to produce a single XML file per each record in the select statement;

2) save each xml file with a unique name based on rowId on a shared server

Note: hardcoding values for each XML row is not an option as my output contains thousands of rows.

The problem here is that FOR XML and XML data type are not allowed in the Cursor statement.

CREATE TABLE #T1 
(
    ID INT NOT NULL,
    LName NVARCHAR(30) NULL,
    FName NVARCHAR(30) NULL,
    Comments NVARCHAR(MAX) NULL
);

GO

INSERT INTO #T1 (ID, LName, FName, Comments)
    VALUES
        (1, 'JONOTHAN', 'SMITH', 'is the best friend ever'),
        (2, 'ROGER', 'SHU`LTS', 'is the boss!'),
        (3, 'Jeremy', 'Deimer', 'is the a good drama actor'),
        (4, 'Alexandra', 'Norusis', 'is the smart feminist');

GO

SELECT 
    t.ID, 
    t.LName, 
    t.FName, 
    t.Comments
FROM #T1 t
    FOR XML PATH(''), ROOT ('body');

Below is the current output of the XML file format:

 <body>
  <ID>1</ID>
  <LName>JONOTHAN</LName>
  <FName>SMITH</FName>
  <Comments>is the best friend ever</Comments>
  <ID>2</ID>
  <LName>ROGER</LName>
  <FName>SHU`LTS</FName>
  <Comments>is the boss!</Comments>
  <ID>3</ID>
  <LName>Jeremy</LName>
  <FName>Deimer</FName>
  <Comments>is the a good drama actor</Comments>
  <ID>4</ID>
  <LName>Alexandra</LName>
  <FName>Norusis</FName>
  <Comments>is the smart feminist</Comments>
</body>

Below is the desired out (based on a record with ID = 1)

        <body>
          <ID>1</ID>
          <LName>JONOTHAN</LName>
          <FName>SMITH</FName>
          <Comments>is the best friend ever</Comments>
        <body>

Solution

I think it's this what you need:

DECLARE @tbl TABLE 
(
    ID INT NOT NULL,
    LName NVARCHAR(30) NULL,
    FName NVARCHAR(30) NULL,
    Comments NVARCHAR(MAX) NULL
);

INSERT INTO @tbl (ID, LName, FName, Comments)
    VALUES
        (1, 'JONOTHAN', 'SMITH', 'is the best friend ever'),
        (2, 'ROGER', 'SHU`LTS', 'is the boss!'),
        (3, 'Jeremy', 'Deimer', 'is the a good drama actor'),
        (4, 'Alexandra', 'Norusis', 'is the smart feminist');

--use this for your CURSOR

SELECT ID
     ,(SELECT t1.* FOR XML PATH('body'),TYPE) AS TheRowAsXml
FROM @tbl AS t1

The result

ID  TheRowAsXml
1   <body><ID>1</ID><LName>JONOTHAN</LName><FName>SMITH</FName><Comments>is the best friend ever</Comments></body>
2   <body><ID>2</ID><LName>ROGER</LName><FName>SHU`LTS</FName><Comments>is the boss!</Comments></body>
3   <body><ID>3</ID><LName>Jeremy</LName><FName>Deimer</FName><Comments>is the a good drama actor</Comments></body>
4   <body><ID>4</ID><LName>Alexandra</LName><FName>Norusis</FName><Comments>is the smart feminist</Comments></body>

UPDATE Your example using a CURSOR

DECLARE @tbl TABLE 
(
    ID INT NOT NULL,
    LName NVARCHAR(30) NULL,
    FName NVARCHAR(30) NULL,
    Comments NVARCHAR(MAX) NULL
);

INSERT INTO @tbl (ID, LName, FName, Comments)
    VALUES
        (1, 'JONOTHAN', 'SMITH', 'is the best friend ever'),
        (2, 'ROGER', 'SHU`LTS', 'is the boss!'),
        (3, 'Jeremy', 'Deimer', 'is the a good drama actor'),
        (4, 'Alexandra', 'Norusis', 'is the smart feminist');

DECLARE @ID INT;
DECLARE @xml XML;

DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR

SELECT ID
      ,(
     SELECT t1.* 
     FOR XML PATH(''),ROOT('body'),TYPE
     )  AS TheXml
FROM @tbl AS t1

OPEN @BusinessCursor;

FETCH NEXT FROM @BusinessCursor INTO @ID, @xml;

WHILE @@FETCH_STATUS = 0 
BEGIN
    --Do something with the values
    PRINT 'ID: ' +  CAST(@ID AS VARCHAR(10));
    PRINT 'XML: ' + CAST(@xml AS NVARCHAR(MAX));
    --Here you can build your BCP command.
    --Use the ID or any other information to build the file's name and save the XML out (use -w -T)

    FETCH NEXT FROM @BusinessCursor INTO @ID, @xml;
END

CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;