December 1, 2008 at 1:59 pm
I am creating an XML file from a SELECT like below:
SELECT
Col1, Col2, Col3
FROM PTable
FOR XML PATH('property'), ROOT('properties'), type
My output in the file is displayed as one contiguous line
I want the output to be one node per line like this in the file
How do I accomplish this?
September 14, 2009 at 3:08 pm
try it this way:
SELECT
Col1, Col2, Col3
FROM PTable as property
--FOR XML PATH('property'), ROOT('properties'), type
FOR XML AUTO, TYPE, ELEMENTS, ROOT('properties'))
September 15, 2009 at 7:31 am
XML is a stream of data. It is not "records". The carriage returns and line feeds that are added are done by a presentation layer such as IE (Internet Explorer), etc.
Once the XML is in a file, why do you need it to be "pretty printed"?
September 28, 2009 at 8:19 pm
Why do I want to read a forum where the "answer" is, you don't need to. Great contribution. Maybe if I can justify my stupid needs to his condescending arrogance he'll grace me with his answer.
I'm having the same problem. I want to create a large test data feed that I can easily read, find the data that is failing and potentially modify some of it for testing. I'm sure some people love doing that on one row of data. I don't.
I can't even get it to stream. It wraps at 256 character columns. Only small amounts of data can be formatted as output to grid, but large amounts produce errors.
The AUTO didn't help in my case.
September 29, 2009 at 7:26 am
grahamcoombes (9/28/2009)
Why do I want to read a forum where the "answer" is, you don't need to. Great contribution. Maybe if I can justify my stupid needs to his condescending arrogance he'll grace me with his answer.It wraps at 256 character columns. Only small amounts of data can be formatted as output to grid, but large amounts produce errors.
The AUTO didn't help in my case.
First thing to notice:
You should stay professional in your posts.
Regarding the issue you're struggling with:
I assume you're talking about the SSMS data grid.
You should increase the number of characters displayed in text modus using the SSMS Tools->Options->Query results->SQL Server -> results to grid. Default is 256.
September 29, 2009 at 12:36 pm
No that did not work for me either with the AUTO. It added stuff I did not want to the XML
Most of the vendors I supply data feeds to take it as is, but I have two vendors that want each node on a separate line format. To get this to work I did the following. I warn you this is nto convential and is ugly but gets the job done for me for now.
I dump the entire ouput of the xml to a table where the column is datatype XML. Then I have a second column in the same table and this column is a data type of TEXT. So what I do then is update the TEXT with the XML column but I do a REPLACE at the end of every node and add on a Char(13). After that is done I then create the file by doing a select * from the TEXT column and I get the data formatted.
[font="Courier New"]--1st get the data
SET @xmlDoc =
(
SELECT top 100 PERCENT
,pid as "Adid"
,COALESCE(AID,OID) AS "ExternalAgentid"
,OLOCATIONID AS "ExternalBrokerid"
FROM [SERVER].MasterTable
FOR XML PATH('Property'), ROOT('Properties'), type
)
CREATE TABLE [dbo].[TableFeedData](
[FeedDate] [datetime] NOT NULL,
[FeedData] [xml] NOT NULL,
[FeedData2] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-- 2nd put the data in the table
INSERT INTO TableFeedData
(feeddate,feeddata,feeddata2)
VALUES
(getdate(),@xmlDoc,'');
-- 3rd format the data and put in carriage returns
UPDATE TableFeedData
SET feeddata2 =
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
CAST(feeddata as varchar(max)),'/>','/>' + char(13) )
,'<Properties>',char(13)+ '<Properties>' + char(13) )
,'<Property>','<Property>' + char(13) )
,'</Adid>','</Adid>' + char(13) )
,'</ExternalAgentid>','</ExternalAgentid>' + char(13) )
,'</ExternalBrokerid>','</ExternalBrokerid>' + char(13) )
,'</Property>','</Property>' + char(13) )
-- 4th write the file
SET @tempFileName = '\\server\data\temp.txt'
SET @bcpCommand = ' bcp "SELECT feeddata2 FROM Server.dbo.TableFeedData" queryout "'
SET @bcpCommand = @bcpCommand + @tempFileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
SELECT @File = '\\server\data\vendor.xml'
SET @bcpCommand ='type \\server\data\temp.txt >> '
SET @bcpCommand = @bcpCommand + @File
EXEC master..xp_cmdshell @bcpCommand
EXEC master..xp_cmdshell 'del \\server\data\.txt'
-- 5th drop table
DROP TABLE TableFeedData[/font]
September 29, 2009 at 10:29 pm
My honest apologies if I misread the arrogance. It just seems obvious to me to see beyond the purist concepts of xml. If an application works perfectly xml data is a stream. If it doesn't, xml data is a long string of unreadable garbage that no-one can work with. Yes I can format it with Visual Studio, etc, but there's something wrong with wanting it to be readable? Maybe carriage returns is a waste of space? Because it's such an efficient format to begin with? Lets get our heads out of the clouds. But I'm doing testing so maybe thats why readability seems like an obvious need to me.
I was aware of the column setting in Options. I didn't imagine that was the answer because I was trying to output 34MB. But I can put that large of a value so it looks like it would work. But it maxes out at 2034 and wraps there.
Thanks for trying to help.
September 30, 2009 at 9:41 am
This seems to be a similar issue to what I was facing. My output files were anywhere from 50K to a couple of Mb so I wanted to be able to send the output to a file and view it. Here is what is working:
vb CLR code snip:
Partial Public Class outputxml
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString)
'Create the variables to hold the values that are supplied by the parameters
'input by the stored procedure
Dim xmlDoc As New XmlDocument()
Dim output As SqlPipe = SqlContext.Pipe()
Try
'Load the result set into the XmlDoc Variable and then save the results in the
'path provided by the stored procedure. The values are provided by the
'input parameters of the stored procedure
xmlDoc.LoadXml(XmlData.Value)
xmlDoc.Save(Filename.Value)
Catch ex As Exception
'If an error occurs catch the message and pipe it back to SQL
output.Send(ex.Message.ToString)
End Try
End Sub
End Class
After creating the CLR, I created a stored procedure:
DECLARE @output xml,
@rundate smalldatetime
SELECT @rundate = getdate()
SET @output = (
SELECT (SELECT RTRIM(AtBats_id) AS partner_id
FROM Branding AS transmitting_party
WHERE co = data.co
FOR XML AUTO, TYPE, ELEMENTS),
(SELECT DISTINCT agent.first_name AS agent_first_name,
agent.last_name AS agent_last_name,
agent.agent_no AS agent_id,
dbo.CleanPhone(agent.telephone) AS agent_phone_number,
dbo.GetEmail(agent.co, agent.agent_no, 2) AS agent_email_address,
'S' AS agent_role,
dbo.GetCompanyName(agent.co) AS company_name,
dbo.GetOfficeName(agent.office, agent.co) AS office,
agent.office AS office_id
FROM Smmst agent
WHERE agent.rec_type = 'A' AND agent.term_date IS NULL
AND agent.co = data.co
ORDER BY agent.agent_no
FOR XML AUTO, TYPE, ELEMENTS, ROOT('agent_data'))
FROM Branding as data
WHERE co = 1
FOR XML AUTO, TYPE, ELEMENTS)
EXEC dbo.outputxml @output, 'c:\temp\AtBatsAgent.xml'
IF @@ERROR = 0
UPDATE dbo.Branding
SET AgentRunDate = @rundate
WHERE co = 1
The output file is nicely formatted XML like this:
<data>
<transmitting_party>
<partner_id>7</partner_id>
</transmitting_party>
<agent_data>
<agent>
<agent_first_name>DONALD</agent_first_name>
<agent_last_name>PEARSON</agent_last_name>
<agent_id>1000</agent_id>
<agent_phone_number>5558629002</agent_phone_number>
<agent_email_address />
<agent_role>S</agent_role>
<company_name>INTERNATIONAL REALTY LLC</company_name>
<office>VENICIAN</office>
<office_id>1</office_id>
</agent>
<agent>
<agent_first_name>EDWARD</agent_first_name>
<agent_last_name>MCGAHAN</agent_last_name>
<agent_id>1001</agent_id>
<agent_phone_number>5558623478</agent_phone_number>
<agent_email_address>EMCGAHAN3@DPN.COM</agent_email_address>
<agent_role>S</agent_role>
<company_name>INTERNATIONAL REALTY LLC</company_name>
<office>VENICIAN</office>
<office_id>1</office_id>
</agent>
</agent_data>
</data>
Hope this helps
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply