Generate separate XML file for each record returned by the query

Question

I have the following query, part of a bigger query, used to generate XML:

DECLARE @XMLProductsLines NVARCHAR(MAX)
SET @XMLProductsLines=''

;WITH XMLNAMESPACES ('http://ITrack.Transmission/2011/02/25/Objects' as q17)

SELECT ProductCode 'q17:ProductCode', OrderedQuantity 'q17:OrderedQuantity'
FROM PurchasesDocumentsLines 
FOR XML RAW ('q17:OrderedProductSection'), ROOT('q17:OrderedProducts'), ELEMENTS

The output from the query is the following:

<q17:OrderedProducts xmlns:q17="http://ITrack.Transmission/2011/02/25/Objects">
  <q17:OrderedProductSection>
    <q17:ProductCode>2139265</q17:ProductCode>
    <q17:OrderedQuantity>5000</q17:OrderedQuantity>
  </q17:OrderedProductSection>
  <q17:OrderedProductSection>
    <q17:ProductCode>2131602</q17:ProductCode>
    <q17:OrderedQuantity>6000</q17:OrderedQuantity>
  </q17:OrderedProductSection>
  <q17:OrderedProductSection>
    <q17:ProductCode>2131601</q17:ProductCode>
    <q17:OrderedQuantity>700</q17:OrderedQuantity>
  </q17:OrderedProductSection>
  <q17:OrderedProductSection>
    <q17:ProductCode>2131416</q17:ProductCode>
    <q17:OrderedQuantity>5000</q17:OrderedQuantity>
  </q17:OrderedProductSection>
</q17:OrderedProducts>

Is there any way to "split" into multiple XML files the results in such way that for every <ProductCode> and <OrderedQuantity> to have different XML files? In this case I should have 4 files. Thanks

DDL

CREATE TABLE PurchasesDocumentsLines
(
ProductCode NVARCHAR (4000),
OrderedQuantity DECIMAL (20, 0)
);

INSERT INTO PurchasesDocumentsLines (ProductCode, OrderedQuantity)
VALUES('2139265', '5000'), ('2131602', '6000'), ('2131601', '700'), ('2131416', '5000');

WITH XMLNAMESPACES('http://ITrack.Transmission/2011/02/25/Objects' AS q17)
SELECT Code AS [q17:DestinationCode]
      ,Description AS [q17:DestinationName]
      ,Address1 AS [q17:DestinationAddress1]
      ,Address2 AS [q17:DestinationAddress2]
FROM PurchasesDocumentsLines
FOR XML PATH('q17:OrderedProducts'),ELEMENTS XSINIL,ROOT('q17:OrderedProductSection');

EDIT

I have declared and used a CURSOR as below but it returns only 1 value (the first in the list). What am I missing?

DECLARE @Number NVARCHAR(MAX)=''
DECLARE @XMLSalesOrders NVARCHAR(MAX)=''
DECLARE @Root NVARCHAR(MAX)=''

DECLARE Records CURSOR FAST_FORWARD

FOR SELECT DISTINCT ProductCode FROM PurchasesDocumentsLines

OPEN Records
FETCH NEXT FROM Records INTO @Number
WHILE @@FETCH_STATUS = 0
BEGIN 
SET @XMLSalesOrders=''

;WITH XMLNAMESPACES ('http://ITrack.Transmission/2011/02/25/Objects' as q17)

SELECT @XMLSalesOrders=( SELECT ProductCode 'q17:ProductCode', OrderedQuantity 'q17:OrderedQuantity'
FROM PurchasesDocumentsLines 
FOR XML RAW ('q17:OrderedProductSection'), ROOT('q17:OrderedProducts'), ELEMENTS)


FETCH NEXT FROM Records INTO @Number
END
CLOSE Records
DEALLOCATE Records

--SET @Root='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">'+@XMLSalesOrders+'</soapenv:Envelope>'

PRINT @Number

Edit 2

Besides generating the XML, I use the following query and a stored procedure taken from a website that save the files on my local laptop. But, after generating the XML in SQL, it my folder is saved just one file. Is there any other way to save the XML files on my laptop, other the one I'm using?

Query that call the procedure

DECLARE @Root NVARCHAR(MAX)=''
SET @Root='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">'+@XMLSalesOrders+'</soapenv:Envelope>'

DECLARE @XMLFileDescription NVARCHAR(250);
SET @XMLFileDescription='StoreDocument'+FORMAT(GETDATE(), 'yyyMMddHHmmssmmm')+'.xml'

EXEC ExportXML @Root,'D:\TT', @XMLFileDescription

Procedure

CREATE PROCEDURE [dbo].[ExportXML] 
  (
    @String Varchar(max), --8000 in SQL Server 2000
    @Path VARCHAR(255),
    @Filename VARCHAR(100)
  )

AS
DECLARE  @objFileSystem int,
         @objTextStream int,
         @objErrorObject int,
         @strErrorMessage Varchar(1000),
         @Command varchar(1000),
         @hr int,
         @fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename
    if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
    if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile' , @objTextStream OUT, @FileAndPath,2,True

    if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='writing to the file "'+@FileAndPath+'"'
    if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String

    if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
    if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'

    if @hr<>0
    begin
    Declare 
        @Source varchar(255),
        @Description Varchar(255),
        @Helpfile Varchar(255),
        @HelpID int

        EXECUTE sp_OAGetErrorInfo  @objErrorObject, @source output,@Description output,@Helpfile output,@HelpID output
        Select @strErrorMessage='Error whilst ' +coalesce(@strErrorMessage,'doing something') +', '+coalesce(@Description,'')
        raiserror (@strErrorMessage,16,1)
    end
EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream
GO

Answer 1

DECLARE @ProductCode AS NVARCHAR(4000)
DECLARE @Quantity AS DECIMAL(20, 0) 

DECLARE Records CURSOR FAST_FORWARD FOR
SELECT  ProductCode 
      , OrderedQuantity 
FROM dbo.PurchasesDocumentsLines

OPEN Records
FETCH NEXT FROM Records INTO @ProductCode, @Quantity
WHILE @@FETCH_STATUS = 0
BEGIN 

WITH XMLNAMESPACES('http://ITrack.Transmission/2011/02/25/Objects' AS q17)
SELECT @ProductCode AS [q17:DestinationCode]
      ,@Quantity AS [q17:DestinationName]
FOR XML PATH('q17:OrderedProducts'),ELEMENTS XSINIL,ROOT('q17:OrderedProductSection');

FETCH NEXT FROM Records INTO @ProductCode, @Quantity
END

CLOSE Records
DEALLOCATE Records