September 25, 2012 at 10:03 am
Hi Friends,
how can I generate xml file with out carriage return\line feed from sql query ?
I can generate xml file from sql query but the output is having carriage return\line feed
Thanks & Regards,
September 25, 2012 at 11:41 am
XML is not format dependent. Any whitespace produced in between markup is strictly for us humans. All XML parsers are going to strip that out (i.e. ignore it). What are you trying to do?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 11:52 am
I am trying to generating the xml file (huge) from the sql query. This xml file is having carriage returns in the xml nodes
September 25, 2012 at 12:12 pm
I am still unclear about your concern. In the data? Or in between the markup tags?
Like this:
<root><element>1
2
3</element></root>
Or like this:
<root>
<element>123</element>
</root>
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 12:18 pm
gsd1 (9/25/2012)
I am trying to generating the xml file (huge) from the sql query. This xml file is having carriage returns in the xml nodes
Understood on that, but as OPC.three was pointing out - so what? Is the XML actually not being processed correctly because of it? Also - have you attempted to change the output codepage/character set? It could be an easy way to cut your file size in half if that's the desired goal (you're looking for ASCII or UTF-8 code pages rather than UTF-16 or 32 which use more space to encode character sets)..
The XML standard essentially doesn't care or doesn't acknowledge the presence of a carriage return, so any standard-compliant XMl process wouldn't care either. If your process does in fact care, then we may need to be concerned about any number of other things to strip out.
If this is causing an actual problem (and it happens regardless of what type of codepage you're using) then then only option I know of is to cast it to varchar and run REPLACE to strip them out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2012 at 12:34 pm
Just to clarify what you mean:
SELECT *
FROM (VALUES (1),(2),(3)) AS TVC(Col)
FOR XML RAW, TYPE;
That query will produce data that does not have carriage-returns in it, but when you open it in the XML viewer in SSMS, it looks like this:
<row Col="1" />
<row Col="2" />
<row Col="3" />
But when you view it as a datagrid query result, it looks like this:
<row Col="1" /><row Col="2" /><row Col="3" />
It doesn't actually have carriage-returns in it, but the XML viewer makes it look like it does. That's not because of anything in the XML, it's a feature of the user-interface (presenation layer).
Is my first example what you are seeing? Or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 25, 2012 at 3:10 pm
Thank you very much for everyone for the quick response.
My sql query to generate xml is
select top 50000 col1,col2,col3,.....col1000
from Table1
FOR XML PATH('Record'), ROOT('Table1')
When I execute this query and save the results to the file, the xml file is like this
<Table1><Record><col1>1</col1><col2>2</col2><col3>3</col3><col4>4</
col4> .......</Record></Table1>
(Note: It is adding CR or LF after so many characters at the end of every line)
Can I get all data in one line with out CR/LF?
Regards,..
September 25, 2012 at 3:16 pm
Which tool are you using to view the XML? As stated before the cr and lf should not be there, but also should not matter.
I have reproduced the effect you describe using notepad with wrap text turned on, although the output is actually only one line of text.
Fitz
September 25, 2012 at 3:32 pm
Mark Fitzgerald-331224 (9/25/2012)
Which tool are you using to view the XML? As stated before the cr and lf should not be there, but also should not matter.I have reproduced the effect you describe using notepad with wrap text turned on, although the output is actually only one line of text.
Fitz
That just jogged a memory. You may run into something different depending on how you're actually outputting the XML to file. If you are simply running the select statement in SSMS, it's going to use the "line length"under the text output settings when you copy it (after it reaches that length you will see the carriage return).
If on the other hand you use BCP to output the query contents with no delimiter (column or row), then no CR/LF.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2012 at 3:53 pm
Mark Fitzgerald-331224
When viewed with notepad, i can see where the CR\LF characters are inserted. when wrap is on...then also data is coming to the next line where ever CR\LF exists.
When view with notepad++, i can see where the CR\LF charecters are inserted.
Matt Miller (#4)
I think you are right, I tried to test this using BCP command ...but couldn't execute "'xp_cmdshell' because of the rights issue. I will test this tomorrow after getting the rights
Thank you very much for the valuable suggestions for both of you....
Regards,
September 26, 2012 at 6:18 am
Matt Miller (#4),
I tested this with BCP command.
If I try to execute the BCP command without entering any delimiter, it is giving error "Enter the file storage type of field "
If I try to execute the BCP command with delimiter -c or -w, the output file is having CR\LF.
Regards,
September 26, 2012 at 7:48 am
Well my case was for a different reason, but I did manage to set the row and column delimiters to the same thing (which helped my scenario). Try \t if you want to try it that way. Otherwise SSIS has some options to just write output to a raw file you might care to review.
Otherwise you'd be best off at this point just cranking the file and cleansing the unwanted stuff out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply