Can you export SQL Server 2012 data into an XML file

Question

Below is a very small example of the SQL and XML but no idea how to export this into say C:\Users\Simon.Evans\Documents\Test.xml. To be fair I have very little to zero knowledge of XML so walking in the dark. Ultimately I am trying to find a way to export for SQL tables into an XML file.

SELECT 
    patient.OID AS '@ID',
    patient.FORENAME, patient.SURNAME,
    patient.TITLECODE,
    patient.BIRTHDTTM
FROM 
    HealthBI.dbo.LZO_PATIENT patient
WHERE patient.OID = '3400000000'
FOR XML PATH('Patient'), ROOT('Patient')

and the XML output of the sql is:

<Patient>
  <Patient ID="3400000000">
    <FORENAME>IDA</FORENAME>
    <SURNAME>CARR</SURNAME>
    <TITLECODE>CC_MRS</TITLECODE>
    <BIRTHDTTM>1929-10-25T00:00:00</BIRTHDTTM>
  </Patient>
</Patient>

score 1 · Answer 1 · answered Apr 11 '17 at 08:27

1

So @dSQL would be your SQL Statment @LocalPath & @Filename would be the path and filename (remember on the server).

You can see the results of the BCP command in the #+bcpOutput table.

  Select @CreateXmlFileCommand = 'bcp "' + @dSQL + '" queryout ' + @LocalPath + @FileName + ' -c -r -t -T -' + @@SERVERNAME;
  select @CreateXmlFileCommand

  CREATE TABLE #_bcpOutput ([Output] VARCHAR(250));         
  INSERT Into #_bcpOutput ([Output]) EXEC master..xp_cmdshell @CreateXmlFileCommand;

answered Apr 11 '17 at 08:27

AntDC

1,807
14
23

1

It should be noted that the `xp_cmdshell` procedure needs to be activated explicitely if you want to use it from a T-SQL script. See http://stackoverflow.com/q/5131491/243373 – TT. Apr 11 '17 at 09:13
1

The switches you provide are not optimal. You might have a look at my answer, why at least `-w` instead of `-c` is important – Shnugo Apr 11 '17 at 09:53

score 1 · Answer 2 · answered Apr 11 '17 at 09:50

Querying XML into a file needs to know a little bit about character encoding...

Try this:

USE master;
GO
CREATE DATABASE TestDB;
GO

CREATE TABLE TestDB.dbo.TestTbl(TestString1 VARCHAR(100),TestString2 NVARCHAR(100));
INSERT INTO TestDB.dbo.TestTbl VALUES('abc',  N'abc')
                                    ,('<&> Russian: "слов в тексте"', N'<&> Russian: "слов в тексте"');
GO                           
SELECT * FROM TestDB.dbo.TestTbl FOR XML PATH('Test'),ROOT('String');

/* The result of the simple SELECT shows, that VARCHAR cannot deal with russian letters
<String>
  <Test>
    <TestString1>abc</TestString1>
    <TestString2>abc</TestString2>
  </Test>
  <Test>
    <TestString1>&lt;&amp;&gt; Russian: "???? ? ??????"</TestString1>
    <TestString2>&lt;&amp;&gt; Russian: "слов в тексте"</TestString2>
  </Test>
</String>
*/
GO

--Two times the same command, only difference is -w instead of -c:  
--And: YOU HAVE TO FULLY QUALIFY THE OBJECT's NAME!                  
DECLARE @cmd1 VARCHAR(4000)='bcp "SELECT * FROM TestDB.dbo.TestTbl FOR XML PATH(''object''),ROOT(''objects'');" queryout "c:\dvp\test1.xml" -T -c -S' + @@SERVERNAME;
DECLARE @cmd2 VARCHAR(4000)='bcp "SELECT * FROM TestDB.dbo.TestTbl FOR XML PATH(''object''),ROOT(''objects'');" queryout "c:\dvp\test2.xml" -T -w -S' + @@SERVERNAME;

EXECUTE master..xp_cmdshell @cmd1;
EXECUTE master..xp_cmdshell @cmd2;
GO
DROP DATABASE TestDb;
GO

The result of the first command with -c will now destroy the russian letters even with NVARCHAR

<objects>
  <object>
    <TestString1>abc</TestString1>
    <TestString2>abc</TestString2>
  </object>
  <object>
    <TestString1>&lt;&amp;&gt; Russian: "???? ? ??????"</TestString1>
    <TestString2>&lt;&amp;&gt; Russian: "???? ? ??????"</TestString2>
  </object>
</objects>

The result of the second command with -w is as expected:

<objects>
  <object>
    <TestString1>abc</TestString1>
    <TestString2>abc</TestString2>
  </object>
  <object>
    <TestString1>&lt;&amp;&gt; Russian: "???? ? ??????"</TestString1>
    <TestString2>&lt;&amp;&gt; Russian: "слов в тексте"</TestString2>
  </object>
</objects>

Can you export SQL Server 2012 data into an XML file

2 Answers2

Linked