Generating Xml File from SQL Server 2008

Using SQL Server 2008 to create an XML file is missing header and footer

As you've found out you cannot out-trick the namespace-issue. Yes, it is a well known issue (the related connect issue has disappaered - after 10 years! - in the meanwhile...)

The approach in one of the answers leads to xmlns="", which is not just a tiny oops, doesn't matter. Alle elements below are not living in the same default namespace!

You'll either have to accept the repeated namespaces (not wrong, but bloating your output and very annoying) or you have to accept more or less ugly workarounds on string base.

In your case I'd suggest to cast the XML to NVARCHAR(MAX) and use simple string concatenation to add prolog and root:

...=N'<?xml ...?><mapi ...> + YourXmlAsString + N'</mapi>';

And you must be aware, that the declaration is not just some silly additive. If you write an encoding of utf-8, your file on disc should be utf-8-encoded actually. Otherwise it would be like a text, where you read on the first page "written in English", but the rest of the book is written in French entirely.

Storing XML file in SQL Server 2008 R2 express

Ok, this is an example for storing the values of the xml into the table instead. I havent't tried this code but it should be working but at least it should clarify how to do as expected.

   /* Imagine your xml looks something like this

    <Content>
        <Title>Text</Title>
        <Value>15</Value>
    </Content>
    */

    CREATE TABLE [dbo].[MyXmlStorage] 
    ( 
        [Id] [int] IDENTITY(1,1) NOT NULL, 
        [Title] [nvarchar](100) NOT NULL, 
        [Value] int NOT NULL,

        CONSTRAINT [PK_MyXmlStorage] 
        PRIMARY KEY CLUSTERED  ([Id] ASC)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

    CREATE PROCEDURE [dbo].[InsertXml]
        (@filePathFull nvarchar(255)) 
    AS 
       DECLARE @xmlAsString VARCHAR(MAX) 
       DECLARE @sql nvarchar(max) 
       DECLARE @xml XML 
       DECLARE @Rms_FileId nvarchar(50) 
       DECLARE @Rms_Id nvarchar(50) 
       DECLARE @Rms_Type nvarchar(50) 
       DECLARE @Rms_Timestamp nvarchar(50) 

    BEGIN 
        SET @sql = 'SELECT @xmlAsString = x.y FROM OPENROWSET( BULK ''' + RTRIM(@filePathFull) + ''', SINGLE_CLOB) x(y)' 
        exec sp_executesql @sql,N'@xmlAsString VARCHAR(MAX) OUTPUT',@xmlAsString OUTPUT 

        set @xml = CONVERT(XML,@xmlAsString)  

        /* Use xpath to query nodes for values inside the Content tag*/
        INSERT INTO MyXmlStorage([Title],[Value]) 
        SELECT 
            x.y.value('title[1]/text()[1]', 'nvarchar(100)') AS title,
            x.y.value('value[1]/text()[1]', 'int') AS value
        FROM @xml.nodes('//Content') AS x(y)
    END 
)

SQL Server 2008 Query Result to XML FIle

Another alternative for this one is bcp utility.

Look into these.

http://www.brighthub.com/internet/web-development/articles/119542.aspx
Enable 'xp_cmdshell' SQL Server

SQL Server 2008 - Add XML Declaration to XML Output

TL;DR

Concatenate this: <?xml version="1.0" encoding="windows-1252" ?> with your XML, converted to varchar(max).

Details

I agree with j0N45 that the schema will not change anything. As the answer he references points out:

You have to add it manually.

I provided some example code to do so in another answer. Basically, you CONVERT the XML into varchar or nvarchar and then concatenate it with the XML declaration, such as <?xml version="1.0" encoding="windows-1252" ?>.

However, it's important to choose the right encoding. SQL Server produces non-Unicode strings according to its collation settings. By default, that will be governed by the database collation settings, which you can determine using this SQL:

SELECT DATABASEPROPERTYEX('ExampleDatabaseName', 'Collation');

A common default collation is "SQL_Latin1_General_CP1_CI_AS", which has a code page of 1252. You can retrieve the code page with this SQL:

SELECT COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'CodePage') AS 'CodePage';

For code page 1252, you should use an encoding name of "windows-1252". The use of "ISO-8859-1" is inaccurate. You can test that using the "bullet" character: •. It has a Unicode Code Point value of 8226 (Hex 2022). You can generate the character in SQL reliably, regardless of collation, using this code:

SELECT NCHAR(8226);

It has also has a code point of 149 in the windows-1252 code page, so you if you are using the common, default collation of "SQL_Latin1_General_CP1_CI_AS", then you can also produce it using:

SELECT CHAR(149);

However, CHAR(149) won't be a bullet in all collations. For example, if you try this:

SELECT CONVERT(char(1),char(149)) COLLATE Chinese_Hong_Kong_Stroke_90_BIN;

You don't get a bullet at all.

The "ISO-8859-1" code page is Windows-28591. None of the SQL Server collations (in 2005 anyway) use that code page. You can get a full list of code pages using:

SELECT [Name], [Description], [CodePage] = COLLATIONPROPERTY([Name], 'CodePage')
FROM ::fn_helpcollations()
ORDER BY [CodePage] DESC;

You can further verify that "ISO-8859-1" is the wrong choice by trying to use it in SQL itself. The following SQL:

SELECT CONVERT(xml,'<?xml version="1.0" encoding="ISO-8859-1"?><test>•</test>');

Will produce XML which does not contain a bullet. Indeed, it won't produce any character, because ISO-8859-1 has no character defined for code point 149.

SQL Server handles Unicode strings differently. With Unicode strings (nvarchar), "there is no need for different code pages to handle different sets of characters". However, SQL Server does NOT use "UTF-8" encoding. If you try to use it within SQL itself:

SELECT CONVERT(xml,N'<?xml version="1.0" encoding="UTF-8"?><test>•</test>');

You will get an error:

Msg 9402, Level 16, State 1, Line 1 XML parsing: line 1, character 38,
unable to switch the encoding

Rather, SQL uses "UCS-2" encoding, so this will work:

SELECT CONVERT(xml,N'<?xml version="1.0" encoding="UCS-2"?><test>•</test>');

Generate single XML file from SQL Server with multiple results per customer

There are two major flaws:

Never create an XML via string concatenation. Just imagine, one of your text columns contains data like 'Do & Co'. The & would break your XML's validity!
Never use culture depending date formats! I did not know, whether your date values are the second of January or the first of February. Always use a secure and independant format such as ISO8601 (especially within XML)!

Try it like this:

There's a mockup scenario which you'll have to adapt to your needs. The basic idea is to use FOR XML PATH() for the billing data and another sub-select - again with FOR XML PATH(),TYPE for the related (nested) activities.

DECLARE @mockupBilling TABLE(StatementDate DATE, ID INT, Balance DECIMAL(10,4),Name VARCHAR(100),Msg VARCHAR(MAX));
INSERT INTO @mockupBilling VALUES(GETDATE(),987654,85.0,'Test Person','You have an outstanding balance.');

DECLARE @mockupActivity TABLE(BillingID INT, ActivityDate DATE, Activity VARCHAR(100), Charge DECIMAL(10,4));
INSERT INTO @mockupActivity VALUES
 (987654,{d'2018-01-02'},'WINTER17D Remaining Balance',50.0)
,(987654,{d'2018-01-03'},'SPRING18A Remaining Balance',15.0);

SELECT b.StatementDate AS [stmt_date]
      ,b.ID AS [id]
      ,b.Balance AS [total_due]
      ,b.Name AS [name]
      ,b.Msg AS [messge]
      ,(
        SELECT a.ActivityDate AS [activity_date]
              ,a.Activity AS [activity_desc]
              ,a.Charge AS [charge]
        FROM @mockupActivity AS a
        WHERE a.BillingID=b.ID
        ORDER BY a.ActivityDate
        FOR XML PATH('activity'),TYPE
       ) 
FROM @mockupBilling AS b
FOR XML PATH('Billing');

The result

<Billing>
  <stmt_date>2018-02-02</stmt_date>
  <id>987654</id>
  <total_due>85.0000</total_due>
  <name>Test Person</name>
  <messge>You have an outstanding balance.</messge>
  <activity>
    <activity_date>2018-01-02</activity_date>
    <activity_desc>WINTER17D Remaining Balance</activity_desc>
    <charge>50.0000</charge>
  </activity>
  <activity>
    <activity_date>2018-01-03</activity_date>
    <activity_desc>SPRING18A Remaining Balance</activity_desc>
    <charge>15.0000</charge>
  </activity>
</Billing>

SQL Server 2008 R2: Export XML BLOB and import back into SQL as table

Unless, there is a need to do indexing / filtering / other analysis on various columns, there is no need to split the xml into various columns.

SQL Server 2008 R2 has xml support and a single table with two column ID and XMLData should work for straight forward storage and retrieval, for example:

CREATE TABLE T1(Col1 int primary key, Col2 xml) 

INSERT INTO T values(1,'<ProductDescription ProductID="1">iPhone</ProductDescription>')

Refer to the msdn article "Implementing XML in SQL Server" for syntax and other help.

Save a SQL query result to an XML file without SSIS or xp_cmdshell

After long digging I found a solution to my problem:

I created a stored procedure like this:

USE [DATABASE NAME]
GO
/****** Object:  StoredProcedure [dbo].[asp_Write_String_To_File]    Script Date:   11/21/2013 09:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [PROCEDUER NAME] 
  (
    @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

then in the query I call the stored procedure like this:

USE [DATABASE NAME]
declare @xml nvarchar(max)
declare @FilePath nvarchar(255)
declare @FileName nvarchar(255)
set @FilePath = '[Path to store file]'       -- Location to store the file
set @FileName = '[File Name to store].xml'   -- This is the XML filename

BEGIN
  set @xml =
      (
        SELECT ( -- Add This
               SELECT FinishCode, Reason, COUNT(*) AS Numbers
                 FROM [DATABASE] ch WITH (NOLOCK)
                WHERE [Condition 1]
                  AND [Condition 2]
        GROUP BY Reason, FinishCode
                ORDER BY Numbers
              FOR XML PATH('FinishCode'),
                 ROOT('UK_Products_Pipeline'),
                 TYPE
                ) -- Add This
         FOR XML PATH('Dial_Stats') -- Add This
   )

exec asp_Write_String_To_File @xml, @FilePath, @FileName

END

This then calls the stored procedure and the file is written to your

How to create XML file by SQL query?

Create your stored procedure like this - use the FOR XML PATH(), ROOT() syntax to have SQL Server generate a proper XML for you:

CREATE PROCEDURE dbo.procGetPlayerScore
AS BEGIN
    SELECT DISTINCT TOP (10)
        ID AS '@ID',   -- creates an attribute on the <Player> node
        Name,          -- gets output as element inside <Player>
        Score          -- gets output as element inside <Player>
    FROM 
        dbo.FB_Players
    ORDER BY 
        Score DESC 
    FOR XML PATH('Player'), ROOT('AllPlayers')
END

In your C# code, you need something like this - connect to the database, execute the stored procedure, get back the single row, single column of that stored procedure (the XML produced):

// set up SQL Server connection and command to execute the stored procedure
using(SqlConnection conn = new SqlConnection("server=.;database=test;Integrated Security=SSPI"))
using (SqlCommand cmdGetPlayers = new SqlCommand("dbo.procGetPlayerScore", conn)) 
{
     // define that it's a stored procedure
     cmdGetPlayers.CommandType = CommandType.StoredProcedure;

     // open connection, execute procedure, get resulting XML, close connection
     conn.Open();
     string playersXml = cmdGetPlayers.ExecuteScalar().ToString();
     conn.Close();
}

As result, you'll get a XML something like this:

<AllPlayers>
    <Player ID="4">
        <Name>Player 4</Name>
        <Score>72.1500</Score>
    </Player>
    <Player ID="1">
        <Name>Player 1</Name>
        <Score>50.5000</Score>
    </Player>
    ......
</AllPlayers>