Show custom automatic headers in excel for sql xml generated file

Question

I'm generating xml files from sql server using a select and the for xml clause The resulting xml files are opened in excel, and I need to add custom headers for each column / field Can I specify custom headers name in the sql script?

As requested by Shnugo, here is a sample of the xml used

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<VendorServices>
  <VendorService>
    <VendorServiceId>0</VendorServiceId>
    <VendorId>0</VendorId>
    <ServiceId>0</ServiceId>
    <Cover>0</Cover>
    <Percentage>0</Percentage>
    <TechRateHr>0</TechRateHr>
    <HelperRateHr>0</HelperRateHr>
  </VendorService>
  <VendorService>
    <VendorServiceId>1</VendorServiceId>
    <VendorId>1</VendorId>
    <ServiceId>1</ServiceId>
    <Cover>0</Cover>
    <Percentage>0</Percentage>
    <TechRateHr>0</TechRateHr>
    <HelperRateHr>0</HelperRateHr>
  </VendorService>
</VendorServices>

I can open it with Excel 2013, Excel ask me if I want to open like xml table and some other options, and then ask about the creation of a schema

How are you opening these files in Excel? Please give more information... — Gottfried Lesigang
– Gottfried Lesigang, Commented Aug 19, 2016 at 20:19
the xml files are associated with excel, so I'm opening from windows explorer directly — Rodrigo Juarez
– Rodrigo Juarez, Commented Aug 19, 2016 at 20:29
Please use the edit option to add a (reduced) example of your xml. I don't think that Excel can open any xml natively ... — Gottfried Lesigang
– Gottfried Lesigang, Commented Aug 20, 2016 at 13:24
@Shnugo question updated with information about the xml used — Rodrigo Juarez
– Rodrigo Juarez, Commented Aug 21, 2016 at 23:43

Gottfried Lesigang · Accepted Answer · 2016-08-22 13:46:07Z

1

I checked with your example. This is not really an answer, but to much for a comment... Maybe it's enough for you to find a full solution.

Well, Excel has (limited) support to add XML natively and treat it as data source. If there is a schema (XSD) included, it will be used, if not, Excel will create a schema from your data automatically.

I used this online tool to create a schema from your example data:

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="VendorServices">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="VendorService" maxOccurs="unbounded" minOccurs="0">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:byte" name="VendorServiceId"/>
              <xs:element type="xs:byte" name="VendorId"/>
              <xs:element type="xs:byte" name="ServiceId"/>
              <xs:element type="xs:byte" name="Cover"/>
              <xs:element type="xs:byte" name="Percentage"/>
              <xs:element type="xs:byte" name="TechRateHr"/>
              <xs:element type="xs:byte" name="HelperRateHr"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

As you can see, your column heads are created from the elements names... I did not find to add a differing information for an element's caption. But maybe there is a way to do this...

If this schema was included in your file or saved as XML-source within Excel you'd at least make the opening process a bit easier.

You might do one of the following:

Export your data with the element names wanted using FOR XML PATH directly from T-SQL (Maybe show your actual query if you need help)
Use XSLT to transform your existing XML
Use VBA or any other programming language for indiv handling
Try to create the XML format, Excel is using itself. XLSXfiles are nothing else than a ZIP-file which you can rename to FileName.zip and open. You'd need the /xl/worksheets/*.xml files only. Maybe sharedStrings.xml. But I think you can learn the basic format easily.

You might read this tutorial for Excel and XSD

answered Aug 22, 2016 at 13:46

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rodrigo Juarez Over a year ago

Thanks for the info, now I'm trying to generate the xml and inline schema with c# code, stackoverflow.com/questions/39047253/…

Gottfried Lesigang Over a year ago

Hi, when you found a solution, please poste it here and accept your own answer. Anybody else might search for the same and find it helpful. If you find my answer helpful, it would be nice to vote it up, thx!

Collectives™ on Stack Overflow

Show custom automatic headers in excel for sql xml generated file

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related