CODEWITHSUNDEEP
xmlexcelt-sql
Rodrigo Juarez
Rodrigo Juarez

Reputation: 1785

Show custom automatic headers in excel for sql xml generated file

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

Upvotes: 1

Views: 185

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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

Upvotes: 1

Related Questions