No Availabel External Columns in XML Source

Question

I got a XML Data file From a Webservice Task,and i want to import data to db(sql server 2008 R2).

In SSIS,use a Data Flow Task,add a XML Source Component,Choose the XML File,Generate XSD file,but no Availabel External Columns in the Columns card.

The following is the XML file:

<?xml version="1.0" encoding="utf-16"?>
<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <TaskName xmlns="http://send.com/">7.26Send</TaskName>
  <Subject xmlns="http://send.com/">Londen Olympic</Subject>
  <SenderName xmlns="http://send.com/" />
  <SenderEmail xmlns="http://send.com/" />
  <ReplyName xmlns="http://send.com/" />
  <ReplyEmail xmlns="http://send.com/" />
  <SendDate xmlns="http://send.com/">2012-07-26T17:00:00</SendDate>
  <FinishDate xmlns="http://send.com/">2012-07-26T17:02:58</FinishDate>
  <Total xmlns="http://send.com/">1267</Total>
  <Sent xmlns="http://send.com/">1267</Sent>
  <HardBounce xmlns="http://send.com/">188</HardBounce>
  <SoftBounce xmlns="http://send.com/">21</SoftBounce>
  <OpenCount xmlns="http://send.com/">105</OpenCount>
  <UniqueOpenCount xmlns="http://send.com/">83</UniqueOpenCount>
  <MailClicked xmlns="http://send.com/">5</MailClicked>
  <LinkClicked xmlns="http://send.com/">0</LinkClicked>
  <AllClicked xmlns="http://send.com/">6</AllClicked>
  <Unsubscribe xmlns="http://send.com/">3</Unsubscribe>
</Report>

Who can tell me how to generate the XSLT and XSD File,Thanks a lot.

Answer 1

How did you generate the XSD? I get errors about Unicode and multiple namespaces.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 2

I think the XML File must be transformed using a XSLT ,But I Cound't generate the XSLT file,so i deleted the part like  "xmlns=http://send.com/ " manually.

After that,generate the XSD in the XML Source.

Answer 3

I also found some threads mentioning XSLT

(for example http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/25915903-de5b-43c0-a635-83ecf538f2e4)

However, I found the issue: the XML is missing some sort of root node. If you add one, you can import the XML file:

<?xml version="1.0"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<Report>
	  <TaskName >7.26Send</TaskName>
	  <Subject >Londen Olympic</Subject>
	  <SenderName  />
	  <SenderEmail  />
	  <ReplyName  />
	  <ReplyEmail  />
	  <SendDate >2012-07-26T17:00:00</SendDate>
	  <FinishDate >2012-07-26T17:02:58</FinishDate>
	  <Total >1267</Total>
	  <Sent >1267</Sent>
	  <HardBounce >188</HardBounce>
	  <SoftBounce >21</SoftBounce>
	  <OpenCount >105</OpenCount>
	  <UniqueOpenCount >83</UniqueOpenCount>
	  <MailClicked >5</MailClicked>
	  <LinkClicked >0</LinkClicked>
	  <AllClicked >6</AllClicked>
	  <Unsubscribe >3</Unsubscribe>
	</Report>
</Root>

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 4

When you are using an xml source, SSIS tries to create a table out of the xml data i.e. it tries to represent the data in the form of tables/ columns and rows. With the xml you just posted it can't figure out how the rows should be created in the table representation. The xml format should be:

<Table>

   <Rows>

       <Columns>

and your xml representation is

<Table>

   <Columns>

If I intorduce a dummy node on top of the expected columns i.e. something like

<?xml version="1.0" encoding="utf-16"?>
<Wrapper>
 <Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <TaskName>7.26Send</TaskName>
  <Subject>Londen Olympic</Subject>
  <SenderName/>
  <SenderEmail/>
  <ReplyName/>
  <ReplyEmail/>
  <SendDate>2012-07-26T17:00:00</SendDate>
  <FinishDate>2012-07-26T17:02:58</FinishDate>
  <Total>1267</Total>
  <Sent>1267</Sent>
  <HardBounce>188</HardBounce>
  <SoftBounce>21</SoftBounce>
  <OpenCount>105</OpenCount>
  <UniqueOpenCount>83</UniqueOpenCount>
  <MailClicked>5</MailClicked>
  <LinkClicked>0</LinkClicked>
  <AllClicked>6</AllClicked>
  <Unsubscribe>3</Unsubscribe>
 </Report>
</Wrapper>

it works fine.

Please refer this for more information.

---

http://btsbee.wordpress.com/

Answer 5

Thanks Koen Verbeeck and btsbee.

You are right.

Another question,because i can't change the vendor's webservice,so i want to transform the XML result using a XSLT.

How to add a root node in the XML file by XSLT file,and how to segregate the other namespace after the elements ?

I created a XSLT,and used it in a XML Task.

The component executed successfully,but the output file was empty.

The following is my XSLT:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
      <xsl:for-each select="Report">
           <Report>           
               <TaskName><xsl:value-of select="TaskName"/></TaskName>           
               <Subject><xsl:value-of select="Subject"/></Subject>           
               <SenderName><xsl:value-of select="SenderName"/></SenderName>           
               <SenderEmail><xsl:value-of select="SenderEmail"/></SenderEmail>           
               <ReplyName><xsl:value-of select="ReplyName"/></ReplyName>           
               <ReplyEmail><xsl:value-of select="ReplyEmail"/></ReplyEmail>           
               <SendDate><xsl:value-of select="SendDate"/></SendDate>           
               <FinishDate><xsl:value-of select="FinishDate"/></FinishDate>           
               <Total><xsl:value-of select="Total"/></Total>           
               <Sent><xsl:value-of select="Sent"/></Sent>
               <HardBounce><xsl:value-of select="HardBounce"/></HardBounce>
               <SoftBounce><xsl:value-of select="SoftBounce"/></SoftBounce>
               <OpenCount><xsl:value-of select="OpenCount"/></OpenCount>
               <UniqueOpenCount><xsl:value-of select="UniqueOpenCount"/></UniqueOpenCount>
               <MailClicked><xsl:value-of select="MailClicked"/></MailClicked>
               <LinkClicked><xsl:value-of select="LinkClicked"/></LinkClicked>
               <AllClicked><xsl:value-of select="AllClicked"/></AllClicked>
               <Unsubscribe><xsl:value-of select="Unsubscribe"/></Unsubscribe>       
         </Report>
    </xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Any help will be appreciated.

Answer 6

XML superman,Please help me.

Answer 7

Use the following Xslt to tranform your document

<?xml version="1.0" encoding="UTF-8"?>
<Wrapper xmlns:ns="http://send.com/">
   <Report>
      <TaskName/>
      <Subject>Londen Olympic</Subject>
      <SenderName/>
      <SenderEmail/>
      <ReplyName/>
      <ReplyEmail/>
      <SendDate>2012-07-26T17:00:00</SendDate>
      <FinishDate>2012-07-26T17:02:58</FinishDate>
      <Total>1267</Total>
      <Sent>1267</Sent>
      <HardBounce>188</HardBounce>
      <SoftBounce>21</SoftBounce>
      <OpenCount>105</OpenCount>
      <UniqueOpenCount>83</UniqueOpenCount>
      <MailClicked>5</MailClicked>
      <LinkClicked>0</LinkClicked>
      <AllClicked>6</AllClicked>
      <Unsubscribe>3</Unsubscribe>
   </Report>
</Wrapper>

The xslt needed a mention of the root node and the namespace was missing. Added that and its working fine. Please use this if you want to test your xslt.

---

http://btsbee.wordpress.com/

Answer 8

Thanks btsbee,your XSLT file had a Compilation error.

I tried another way,but i still have a question.

First,i used a XSLT file to add ROOT node,like this:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://send.com/">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="FocusReport">
       <ROOT>
          <Report>
             <xsl:copy-of select="node()"/>
          </Report>
       </ROOT>
</xsl:template>
</xsl:stylesheet>

I got a new XML file,like this:

  <?xml version="1.0" encoding="utf-8" ?>

- <ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns=http://send.com/>

- <Report>

  <TaskName>7.26Send</TaskName>

  <Subject>Londen Olympic</Subject>

  <SenderName />

  <SenderEmail />

  <ReplyName />

  <ReplyEmail />

  <SendDate>2012-07-26T17:00:00</SendDate>

  <FinishDate>2012-07-26T17:02:58</FinishDate>

  <Total>1267</Total>

  <Sent>1267</Sent>

  <HardBounce>188</HardBounce>

  <SoftBounce>21</SoftBounce>

  <OpenCount>118</OpenCount>

  <UniqueOpenCount>92</UniqueOpenCount>

  <MailClicked>6</MailClicked>

  <LinkClicked>0</LinkClicked>

  <AllClicked>8</AllClicked>

  <Unsubscribe>3</Unsubscribe>

  </Report>

 </ROOT>

Now,the XML file had a ROOT node,but it had multiple namespace,the XML Source couldn't read the data in SSIS.

Can you tell me how to load the data ?thanks

Answer 9

My bad Andrew.Shi. I pasted the transformed xml instead of the xslt :(. Please try the following xslt.<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:ns="http://send.com/">
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="/Report">
    <Wrapper>
      <Report>
        <TaskName>
          <xsl:value-of select="//ns:TaskName"/>
        </TaskName>
        <Subject>
          <xsl:value-of select="Subject"/>
        </Subject>
        <SenderName>
          <xsl:value-of select="SenderName"/>
        </SenderName>
        <SenderEmail>
          <xsl:value-of select="SenderEmail"/>
        </SenderEmail>
        <ReplyName>
          <xsl:value-of select="ReplyName"/>
        </ReplyName>
        <ReplyEmail>
          <xsl:value-of select="ReplyEmail"/>
        </ReplyEmail>
        <SendDate>
          <xsl:value-of select="SendDate"/>
        </SendDate>
        <FinishDate>
          <xsl:value-of select="FinishDate"/>
        </FinishDate>
        <Total>
          <xsl:value-of select="Total"/>
        </Total>
        <Sent>
          <xsl:value-of select="Sent"/>
        </Sent>
        <HardBounce>
          <xsl:value-of select="HardBounce"/>
        </HardBounce>
        <SoftBounce>
          <xsl:value-of select="SoftBounce"/>
        </SoftBounce>
        <OpenCount>
          <xsl:value-of select="OpenCount"/>
        </OpenCount>
        <UniqueOpenCount>
          <xsl:value-of select="UniqueOpenCount"/>
        </UniqueOpenCount>
        <MailClicked>
          <xsl:value-of select="MailClicked"/>
        </MailClicked>
        <LinkClicked>
          <xsl:value-of select="LinkClicked"/>
        </LinkClicked>
        <AllClicked>
          <xsl:value-of select="AllClicked"/>
        </AllClicked>
        <Unsubscribe>
          <xsl:value-of select="Unsubscribe"/>
        </Unsubscribe>
      </Report>
    </Wrapper>
  </xsl:template>
</xsl:stylesheet>

---

http://btsbee.wordpress.com/

Answer 10

Sorry ,btsbee,ahaha,thanks a lot.

I had killed the question,OY!

I used two XML Task, and two XSLT file,after that i got a XML file that no multiple namespaces ,and add a ROOT Node.

The First XSLT to add ROOT node:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://send.com/">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="FocusReport">
       <ROOT>
          <Report>
             <xsl:copy-of select="node()"/>
          </Report>
       </ROOT>
</xsl:template>
</xsl:stylesheet>

 The Second XSLT to delete namespaces:

 <?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0"         xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
 <xsl:output method="xml" indent="no" />  
<xsl:template match="/|comment()|processing-instruction()">    
       <xsl:copy>      
             <xsl:apply-templates />    
       </xsl:copy>  
</xsl:template>  
<xsl:template match="*">    
<xsl:element name="{local-name()}">      
<xsl:apply-templates select="@*|node()" />   
 </xsl:element>  
</xsl:template>  
<xsl:template match="@*">    
<xsl:attribute name="{local-name()}">      
<xsl:value-of select="." />    
</xsl:attribute>  
</xsl:template>
</xsl:stylesheet>

Now i got the correct  file that the XML Source could load.

Thank you !