locked
Import XML in SSIS. How can I get elements at the top of the xml page to import. RRS feed

  • Question

  • Hi,

    I have an XML file but as well as importing fields in the main body i need to import a date field which is in the first line of the xml page. How do I do this. If there are any good articles on how to import xml files and choose which elements i can pull in that would be useful or if anyone can just tell me.

    There are xslt and xsd files which i don't really understand but i presume these need to be created or configured in order to import using an xml source.

    This is a sample of the file i am trying to import. I already have it set up and working for the elements in the row but I need to get the '
    createDate' within the first section 'report name'

    Any help would be appreciated, Thanks

    <?xml version="1.0" encoding="UTF-8" ?>
    - <report name="report generated through xx.exe" masterAccountID="584367" masterAccountName="xxxx" dateStart="2008-11-02+0000" dateEnd="2008-11-02+0000" booksClosedTimestamp="2008-11-03T07:30:00.000+0000" booksClosed="true" createDate="2008-11-03T08:00:10.669+0000" sortColumn="xxxx" sortOrder="asc">
    - <totals>
      <xxxx="148778" numxx="1150" ctr="0.007729637446396645" xxx="0.18750434782608696" cost="0" xxx="0" />
      </totals>
    + <row xxx="42839531" xxxx="402803419" xID="7310355031" xxID="889773531" ---ID="-8339962739566952784" xxx="21" accountx="xxxxxx" xxx="1998155802" xxxxxName="broadband1" xxx="Uxx & x" Name="xxxx" xxxxName="Uxxxve" url="http://xxx.com/" xxx="2">
      <analytics numImpr="6" numClick="0" ctr="0.0" cost="0.0" averagePosition="10.0" />
      </row>


    Tuesday, November 4, 2008 6:59 PM

Answers

  • Apart from what Matt already said there is one more way..

     

    Create a Script task.

     

    use a XmlTextReader to parse the XML.

     

    then get the data

      Dim MyElement = m_xmlr.GetAttribute("report name")

    assign this to a variable in your package

     

    after that you can use it anywhere

     

     

    here is code that will help you play with you XML in VB.NET

    http://www.codeproject.com/KB/cpp/parsefilecode.aspx

    Wednesday, November 19, 2008 3:10 PM

All replies

  • I am not an XML source expert but I don't believe you can do this in the xml source.  I do believe you could use the xml task to extract the value into a variable and then use a derived column transform to inject that value into the dataflow.

     

    Matt

     

    Tuesday, November 4, 2008 8:47 PM
  • Hi,

    Thanks, I have used an XML Source Editor to to import the columns from the XML file. I specified my XML file in the XML Location and then generated and XSD file using the 'Generate XSD' button. After this in the columns section (still in the XML Source Editor) the 'Output name' drop down box lists 4 sections
    <totals> <row.. etc with the corresponding fields BUT it does not show the:

    <report name=.......

    which is the first row in the XML file and this row contains the date field that I need as well as the other field. Any ideas how I can get the information in the first row.

    Has the XSD file got anything to do with it? As I generated the XSD file automatically i am assuming it automatically chooses what to pull through. Is there anyway I can tell it to look at the first row as well? or how do I create an XSD file that will pull in all the fields Iincluding my first row?

    Thanks in advance
    Wednesday, November 19, 2008 12:05 PM
  • Apart from what Matt already said there is one more way..

     

    Create a Script task.

     

    use a XmlTextReader to parse the XML.

     

    then get the data

      Dim MyElement = m_xmlr.GetAttribute("report name")

    assign this to a variable in your package

     

    after that you can use it anywhere

     

     

    here is code that will help you play with you XML in VB.NET

    http://www.codeproject.com/KB/cpp/parsefilecode.aspx

    Wednesday, November 19, 2008 3:10 PM