How do I get FOR XML to write data with carriage return/line feed at end of elements?

Question

 

Hi,

I am trying to use FOR XML under SQL Server 2014 to write out a large XML data set.

I want it to look like

<CVS_Member_Add_Change>
    <RecordType>3</RecordType>
    <Carrier>1266</Carrier>
    <MultiBirthCode>0000000</MultiBirthCode>
    <MemberType></MemberType>
    <LanguageCode>1</LanguageCode>
    <DURFlag></DURFlag>
    <DURKey></DURKey>
    <SocialSecurityNumber>000000000</SocialSecurityNumber>
</CVS_Member_Add_Change>

That's how it looks when you click on the results of a small subset of the query.  Just what I want.  Unfortunately when you try to right click and save it you get 

<dataroot><CVS_Member_Add_Change><RecordType>3</RecordType><Carrier>1266</Carrier<MultiBirthCode>0000000</MultiBirthCode><MemberType></MemberType<LanguageCode>1</LanguageCode><DURFlag></DURFlag><DURKey></DURKey><SocialSecurityNumber>000000000</SocialSecurityNumber</CVS_Member_Add_Change>

Everything being on one line blows up the translator application that reads the data.

The FOR XML statement copied out of the query is below.

FOR XML RAW ('CVS_Member_Add_Change'), ROOT('dataroot'), ELEMENTS 
GO

Is there a way in the T-SQL to force it to break lines neatly?

Is there a way to force it to a specific file name or directory?

Thank you for your time.

Answer 1

Everything being on one line blows up the translator application that reads the data.

Then you should talk to the vendor/developer responsible for that application. Line breaks means nothing in XML, and an XML applicaiton that chokes on XML without line breaks is seriously broken.

No, there is no such option. The XML support is better in .Net and may be better to construct the XML in a .NET program, although I would not surprised to learn if neither .NET does not supply any option to control white space.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

See if using xml:space attribute helps with your problem.

declare @x xml = '
<CVS_Member_Add_Change xml:space="preserve">
  <RecordType>3</RecordType>
  <Carrier>1266</Carrier>
  <MultiBirthCode>0000000</MultiBirthCode>
  <MemberType />
  <LanguageCode>1</LanguageCode>
  <DURFlag />
  <DURKey />
  <SocialSecurityNumber>000000000</SocialSecurityNumber>
</CVS_Member_Add_Change>
';

select @x as [@x];
GO

Save the result to a file named test.xml and open the file.

http://www.w3.org/TR/2004/REC-xml11-20040204/#sec-white-space

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas