Export XML Column to Excel

Discussion in 'microsoft.public.sqlserver.dts' started by odeddror, Jun 5, 2010.

  1. odeddror

    odeddror Guest

    Hi there,

    I'm using SQL Server 2008
    And I have a table called myProgress with column name summary data type xml

    looks like that

    <?xml version="1.0"?>
    <BOOKS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="BooksSchema.xsd">
    <BOOK>
    <TITLE>The Adventures of Huckleberry Finn</TITLE>
    <AUTHOR>Mark Twain</AUTHOR>
    <BINDING>mass market paperback</BINDING>
    <PAGES>298</PAGES>
    <PRICE>5.49</PRICE>
    </BOOK>
    <BOOK>
    <TITLE>Crystal Reports 10</TITLE>
    <AUTHOR>some one</AUTHOR>
    <BINDING>mass market paperback</BINDING>
    <PAGES>150</PAGES>
    <PRICE>50.90</PRICE>
    </BOOK>
    </BOOKS>

    and the xsd looks like that

    <?xml version="1.0" encoding="utf-8"?>
    <xs:schema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns=""
    id="BOOKS" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element msdata:IsDataSet="true" msdata:UseCurrentLocale="true"
    name="BOOKS">
    <xs:complexType>
    <xs:choice minOccurs="0" maxOccurs="unbounded">
    <xs:element name="BOOK">
    <xs:complexType>
    <xs:sequence>
    <xs:element minOccurs="0" name="TITLE" type="xs:string" />
    <xs:element minOccurs="0" name="AUTHOR" type="xs:string" />
    <xs:element minOccurs="0" name="BINDING" type="xs:string" />
    <xs:element minOccurs="0" name="PAGES" type="xs:string" />
    <xs:element minOccurs="0" name="PRICE" type="xs:string" />
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:choice>
    </xs:complexType>
    </xs:element>
    </xs:schema>


    Now I want to create an SSIS package that export this xml colum into Excel
    spreadsheet
    looks like that

    Title Author Binding Pages Price
    line 1
    line 2 Extc...

    How do I do that?

    Every time I try it look likes one long string
    It's seems that ssis don't see an xml data type

    Thanks,
    Oded Dror
     
    odeddror, Jun 5, 2010
    #1
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.