Export to csv file

I have created an xml file that I want to export to excel.  The file has 50+ columns accross the top.  It is currently only allowing 18 columns.  Does anyone know how to allow more columns?  I have attached my file
Who is Participating?
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Agree with pravinasar...

Your Stored Procedures are using XML AUTO and that is not going to work for you.

You need a more traditional XML construct and the easiest way to achieve that is to use at least the PATH() and ROOT() qualifiers instead of AUTO.

I see you are using the row_number() function, so, no problems using PATH() and ROOT().

For example (and doing a small example manually)

if object_id('tempdb..#my_xml_test','U') is not null drop table #my_xml_test

create table #my_xml_test (id int, code varchar(10), description varchar(100))

insert #my_xml_test values (1,'AR-5381','Adjustable Race')
insert #my_xml_test values (2,'BA-8327','Bearing Ball')
insert #my_xml_test values (3,'BE-2349','BB Ball Bearing')
insert #my_xml_test values (4,'E-2908','Headset Ball Bearings')

select * from #my_xml_test for xml auto

select * from #my_xml_test for xml path('rows'), root('data')

Open in new window

That will produce two results. Click on the first result content (ie the AUTO output) and it will open a new window where you will see something like :

<_x0023_my_x005F_xml_test id="1" code="AR-5381" description="Adjustable Race" />
<_x0023_my_x005F_xml_test id="2" code="BA-8327" description="Bearing Ball" />
<_x0023_my_x005F_xml_test id="3" code="BE-2349" description="BB Ball Bearing" />
<_x0023_my_x005F_xml_test id="4" code="E-2908" description="Headset Ball Bearings" />

Open in new window

Now click on the second one (the PATH() and ROOT()) and you will see something like :

    <description>Adjustable Race</description>
    <description>Bearing Ball</description>
    <description>BB Ball Bearing</description>
    <description>Headset Ball Bearings</description>

Open in new window

Which does look a lot more like a traditional XML file.

You can then copy and paste into Notepad and save those to disk with the XML file type.

Double clicking on them will invoke the standard XML viewer (internet explorer normally) and the first one doesnt open, while the second one does open.

You can also try opening in Excel. The first one wont (error parsing), and the second one will (might prompt for a couple of things - just press enter). You dont actually need a schema. Note, for Excel to open, never use a ROOT of 'table' excel will try to do something different with that.

Now, your question asked for an export to CSV, so, will the above information solve your problem, or do you still want to export to CSV. Also, how are you running the procedures and can you produce the disk file ?
Jeff DarlingDeveloper AnalystCommented:
does your xml have more than 1 table?

I'm not seeing the attachment.
tracimAuthor Commented:
I have attached it below - not sure what you mean more than one table.  I'm a newbie with xml..........
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Geert BormansInformation ArchitectCommented:

well aparently you are taking a good approach... nested XML to csv is best helped by using XSLT.

The XSLT however is very hardcoded, leaving you you little option of extending the data in the XML.

Can you please attach the source XML and indicate the extra columns you need?
I am happy to rewrite this XSLT for you
tracimAuthor Commented:

I have attached two files below.  The first one is a screenshot of the report that shows all of the columns that are needed.  The 2nd file is my source file called CSVLCSPerformanceReport.xsl.  I appreciate your help.
Geert BormansInformation ArchitectCommented:
the 2nd file is the XSLT that does the transformation to CSV.
What I need is the source XML that is the input to the XSLT process
tracimAuthor Commented:
Hi there,

The attached below is the file that produces the report.  The only other files that are used for the report are the stored procedures. Let me know if you need the stored procedures too.  Thanks!
Geert BormansInformation ArchitectCommented:
let me explain something.
XSLT (you have sent a bunge of XSLT files already)
is a programming language that transforms an XML source into something else
this means that as a starting point you can only have XML.
I need to see that XML in order to change your XSLT process (or at least to test it)

It could be that the XML is dynamically generated from a database
and that the query result is an XML object that is directly fead into an XSLT process

Can you give some more insight on that?

Does the LCSPerformanceReport.xsl give you the report as I can see it in the .docx file?
It seems to be the case.
That could help me to generate the CSV for you

some info on database type, platform, ... would definitely build the understanding
tracimAuthor Commented:
Thanks for your patience.  I am new at this.  Yes, the xml is generated from the two stored procedures that I have attached.  Yes, the LCSPerformance Reports gives the report in the .docx file.  The csv file should match that report.

The database is SQL Server.  Please let me know if you have any questions.
tracimAuthor Commented:
Pravin AsarPrincipal Systems EngineerCommented:
Which version of Microsoft Office you are using ?

Is it 2010 or 2007 ?

With Excel 2010, you can open XML File, as  XML Table.
It can auto create XSD.
Pravin AsarConnect With a Mentor Principal Systems EngineerCommented:
Also consider adding the option for XML output.



This add appropriate wrapper tags to each record and entire record set
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
Mark WillsTopic AdvisorCommented:
The error is the stored procedure using XML AUTO

If that is changed to PATH() and ROOT() then a correctly formatted XML file is created.

That file can then be read directly by Excel.

The extra step of needing CSV was never confirmed and almost irrelevant considering the XML should now be properly formatted and readable.

Please see : http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/XML/Q_27982980.html#a38781305
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.