Post reply

Importing XML through SSIS

  • March 24, 2010 at 2:57 pm

    #1139256

    Thank you so much Wayne.

    Yes I am very tired, I have invested alot of time trying to figure this out so was making silly mistakes.

    --
    :hehe:

  • March 24, 2010 at 3:39 pm

    #1139280

    Thanx Wayne for taking over.

    I've been busy in between so I couldn't follow up for a while...

    @Slick: Glad it worked. 😀

    Sometimes it's easier to rely on T-SQL including some new functionality like XQuery than to try using SSIS for everything... 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 24, 2010 at 4:13 pm

    #1139290

    What have you tried?

    I just created an XMLsource, let it generate a schema for it, then tied the two together and dumped the results to a table. Data ensues.

    Where are you getting stuck? If the preview from within the XML source showing any data in it?

    <edit> nvermind - didn't notice how many responses you already had!

    </edit>

    Sounds like you've managed it....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • March 24, 2010 at 4:24 pm

    #1139294

    lmu92 (3/24/2010)

    Thanx Wayne for taking over.

    I've been busy in between so I couldn't follow up for a while...

    No problem. On the other hand, it's been a little bit slow for me this week...

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 24, 2010 at 7:06 pm

    #1139339

    Hello Matt,

    Im not sure how you pulled it off so easily? Did you have an xml with multiple nested elements?

    What exact steps did you take? I would be interested to know!

    Also to answer your question, no the preview is not showing any results to me.

    This is what I've done. I take a Data Flow Task, double click and go inside it. I add a XML Source in it. I double click it and define the hard-coded path of my xml file and the generated XSD. Click ok.

    I add OLEDB destination in this same data flow as well. Pull the green arrow FROM XML Source and stick it into OLEDB destination. In OLEDB , I define the connection manager to use and the table. Click on preview and see jack.

    Thanks,

    Slick

    --
    :hehe:

  • March 24, 2010 at 8:26 pm

    #1139353

    I usually just start easy. Having been bitten before by trying too much difficulty at once with SSIS, I "simplify the scenario" first: make a single level work with Preview, then add onto that. In my case - I started with a single level XML, but you could just use a simplified schema to just just show the level you want. Once I added in the second level - it didn't like my supplied schema anymore, so I made sure to let it generate the schema for me, which them gave me access to the second level.

    once I did that - I started getting multiple xml "sources" that I could start mapping. (look at the column mapping area in the XML source object). Once you have the multiple "source" outputs (i.e. tables), the rest is simple.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • March 25, 2010 at 6:52 am

    #1139578

    Matt,

    Thank you for the tip. I will go ahead and try to do the same.

    Lutz,

    In your query like below:

    DECLARE @xml XML

    SET @xml = (SELECT * FROM OPENROWSET(BULK 'c:\filepath\variablefileName', SINGLE_BLOB) AS x)

    Can I somehow use a variable filename? This is because my filename is changing (its timestamped so each day its a that days date).

    Thanks,

    S

    --
    :hehe:

  • March 25, 2010 at 7:52 am

    #1139639

    Matt,

    I was able to import the XML at the very simple level. But it seems very tedious to define multiple XSDs for each level as my XML file is very complex and has multiple nestings.

    I will just go ahead & use the OPENROWSET which seems much more flexible and easier at this point. Though the benefit of having the tables created through SSIS will be lost.

    If you can take my post earlier and use the XML file I provided and import that successfully, please let me know. I have so far been unsuccessful in importing more than one element. The "Product" element appears multiple times and even with specifying "maxOccurs='unbounded'" I still run into problems with other columns, etc.

    Thanks,

    S

    --
    :hehe:

  • March 25, 2010 at 9:40 am

    #1139743

    Slick84 (3/25/2010)

    ...Lutz,

    In your query like below:

    DECLARE @xml XML

    SET @xml = (SELECT * FROM OPENROWSET(BULK 'c:\filepath\variablefileName', SINGLE_BLOB) AS x)

    Can I somehow use a variable filename? This is because my filename is changing (its timestamped so each day its a that days date).

    Thanks,

    S

    Hi Slick,

    I guess you'd have to use dynamic SQL for that...

    Something like

    DECLARE @t VARCHAR(100)

    SET @t='YourFileLocationAndName'

    DECLARE @SQLString NVARCHAR(500);

    DECLARE @ParmDefinition NVARCHAR(500);

    DECLARE @xmlChar VARCHAR(MAX)

    DECLARE @xml XML

    SET @SQLString = N'SET @xmlOUT =(SELECT * FROM OPENROWSET(BULK ''' + @t + ''', SINGLE_BLOB) AS x)';

    SET @ParmDefinition = N'@xmlOUT varchar(max) OUTPUT';

    EXECUTE sp_executesql @SQLString, @ParmDefinition, @xmlOUT=@xmlChar OUTPUT;

    SET @xml=CAST(@xmlChar AS XML)

    SELECT @xml;


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 25, 2010 at 9:43 am

    #1139745

    Great, Thanks.

    I actually used SSIS to rename it to a static name. I will now use your script to run the imports!

    Thank you!

    S

    --
    :hehe:

  • March 25, 2010 at 10:26 am

    #1139791

    Slick84 (3/25/2010)

    Matt,

    I was able to import the XML at the very simple level. But it seems very tedious to define multiple XSDs for each level as my XML file is very complex and has multiple nestings.

    I will just go ahead & use the OPENROWSET which seems much more flexible and easier at this point. Though the benefit of having the tables created through SSIS will be lost.

    If you can take my post earlier and use the XML file I provided and import that successfully, please let me know. I have so far been unsuccessful in importing more than one element. The "Product" element appears multiple times and even with specifying "maxOccurs='unbounded'" I still run into problems with other columns, etc.

    Thanks,

    S

    I simply regenerate the same schema over and over, as I add in the extra layers. Each layer takes literally a few minutes to test out, so it wasn't too bad. Once I saw what the schema (that SSIS wanted) was doing, it got to be a lot easier. On reasonably simple ones, I could probably do it from scratch all of the way through right now, I just prefer to know if I did it right each step fo the way.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • April 5, 2010 at 7:34 pm

    #1146162

    slick,

    were you able to resolve your issue? I am having similar issue. I'm reading in InfoPath XML file and used XML Source data flow item to read it in. I generated xsd file using xsd.exe and all the columns were available in the mapping pane. When I try to insert the data into database or flatfile, I don't see any data coming across. I know there's data in the XML file! I used another sample xml and xsd file and those are working fine.

    I don't understand why it won't work!

    My xml file is:

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

    <?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:SF424e:-myXSD" solutionVersion="1.0.0.134" productVersion="12.0.0.0" PIVersion="1.0.0.0" href="https://moss.usvipfa.com/FormServerTemplates/SF424e.xsn"?>

    <?mso-application progid="InfoPath.Document" versionProgid="InfoPath.document.2"?>

    <my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">

    <my:sf424_Revision></my:sf424_Revision>

    <my:sf424_Preapplication>false</my:sf424_Preapplication>

    <my:sf424_NewApplication>true</my:sf424_NewApplication>

    <my:sf424_Application>true</my:sf424_Application>

    <my:sf424_ContinuationApplication>false</my:sf424_ContinuationApplication>

    <my:sf424_SelectOther></my:sf424_SelectOther>

    <my:sf424_CorrApplication>false</my:sf424_CorrApplication>

    <my:sf424_RevisionApplication>false</my:sf424_RevisionApplication>

    <my:sf424_ApplicantIdentifier></my:sf424_ApplicantIdentifier>

    <my:sf424_FederalEntityIdentifier></my:sf424_FederalEntityIdentifier>

    <my:sf424_FederalAwardIdentifier></my:sf424_FederalAwardIdentifier>

    <my:sf424_StateRecDate>2009-04-23</my:sf424_StateRecDate>

    <my:sf424_StateApplicationIdentifier>N/A</my:sf424_StateApplicationIdentifier>

    <my:sf424_LegalName>VIRGIN ISLANDS ENERGY OFFICE</my:sf424_LegalName>

    <my:sf424_TaxpayerIdentification>66-0711200</my:sf424_TaxpayerIdentification>

    <my:sf424_OrgDuns>149654258</my:sf424_OrgDuns>

    <my:sf424_Street1>NO. 45 MARS HILL</my:sf424_Street1>

    <my:sf424_Street2></my:sf424_Street2>

    <my:sf424_City>FREDERIKSTED</my:sf424_City>

    <my:sf424_Country1></my:sf424_Country1>

    <my:sf424_State>VI: Virgin Islands of the</my:sf424_State>

    <my:sf424_Province>N/A</my:sf424_Province>

    <my:sf424_Country2>USA</my:sf424_Country2>

    <my:sf424_Zip>00840-4474</my:sf424_Zip>

    <my:sf424_DepartmentName>VIRGIN ISLANDS ENERGY OFFICE</my:sf424_DepartmentName>

    <my:sf424_DivisionName></my:sf424_DivisionName>

    <my:sf424_Prefix></my:sf424_Prefix>

    <my:sf424_FirstName>Bevan</my:sf424_FirstName>

    <my:sf424_MiddleName>R.</my:sf424_MiddleName>

    <my:sf424_LastName>Smith</my:sf424_LastName>

    <my:sf424_Suffix>Jr.</my:sf424_Suffix>

    <my:sf424_Title>Director</my:sf424_Title>

    <my:sf424_OrgAffiliation></my:sf424_OrgAffiliation>

    <my:sf424_FaxNumber>(340) 772-2133</my:sf424_FaxNumber>

    <my:sf424_TelephoneNumber>(340) 773-1082 X2254</my:sf424_TelephoneNumber>

    <my:sf424_Email>bsmith@vienergy.org</my:sf424_Email>

    <my:sf424_ApplicantType1>A. State Government</my:sf424_ApplicantType1>

    <my:sf424_ApplicantType2>F. U.S. Territory or Possession</my:sf424_ApplicantType2>

    <my:sf424_ApplicantType3></my:sf424_ApplicantType3>

    <my:sf424_ApplicantTypeOther></my:sf424_ApplicantTypeOther>

    <my:sf424_FederalAgency>National Energy Technology Laboratory</my:sf424_FederalAgency>

    <my:sf424_AssistanceNumber>81.041</my:sf424_AssistanceNumber>

    <my:sf424_CFDATitle>State Energy Program</my:sf424_CFDATitle>

    <my:sf424_FundingOpportunityNumber>DE-FOA-0000052</my:sf424_FundingOpportunityNumber>

    <my:sf424_Title1>Recovery Act - State Energy Program</my:sf424_Title1>

    <my:sf424_CompetitionIdentificationNumber></my:sf424_CompetitionIdentificationNumber>

    <my:sf424_Title2></my:sf424_Title2>

    <my:sf424_AreasAffected>United States Virgin Islands: St. Thomas, St. John, Water Island and St. Croix</my:sf424_AreasAffected>

    <my:sf424DescriptiveTitle>Promote energy efficiency, advocate the use of renewable technology for residents of the Virgin Islands through capital investments, energy education, financial incentives, and technical assistance.</my:sf424DescriptiveTitle>

    <my:sf424_CongressionalApplicant>00-000</my:sf424_CongressionalApplicant>

    <my:sf424_CongressionalProject>00-000</my:sf424_CongressionalProject>

    <my:Text53></my:Text53>

    <my:sf424_EndDate>2012-04-30</my:sf424_EndDate>

    <my:sf424_StartDate>2009-04-23</my:sf424_StartDate>

    <my:sf424_EstFundFederal>20678000.0</my:sf424_EstFundFederal>

    <my:sf424_EstFundApplicant>0.0</my:sf424_EstFundApplicant>

    <my:sf424_EstFundState>0.0</my:sf424_EstFundState>

    <my:sf424_EstFundLocal>0.0</my:sf424_EstFundLocal>

    <my:sf424_EstFundOther>0.0</my:sf424_EstFundOther>

    <my:sf424_EstFundProgramIncome>0.0</my:sf424_EstFundProgramIncome>

    <my:sf424_EstFundTotal>20678000.0</my:sf424_EstFundTotal>

    <my:sf424_ExecOrder12372Date>2009-05-07</my:sf424_ExecOrder12372Date>

    <my:sf424_ExecOrder2372Rev>true</my:sf424_ExecOrder2372Rev>

    <my:sf424_ExecOrder12372Subject>false</my:sf424_ExecOrder12372Subject>

    <my:sf424_ExecOrder12372Not>false</my:sf424_ExecOrder12372Not>

    <my:sf424_ApplicantDelinquentExplain></my:sf424_ApplicantDelinquentExplain>

    <my:sf424_ApplicantDelinquentYes>false</my:sf424_ApplicantDelinquentYes>

    <my:sf424_ApplicantDelinquentNo>true</my:sf424_ApplicantDelinquentNo>

    <my:sf424_Agree>true</my:sf424_Agree>

    <my:sf424_ARPrefix></my:sf424_ARPrefix>

    <my:sf424_ARFirstName>Bevan</my:sf424_ARFirstName>

    <my:sf424_ARMiddleName>R.</my:sf424_ARMiddleName>

    <my:sf424_ARLastName>Smith</my:sf424_ARLastName>

    <my:sf424_ARSuffix>Jr.</my:sf424_ARSuffix>

    <my:sf424_ARTitle>Director</my:sf424_ARTitle>

    <my:sf424_ARPhone>(340) 773-1082 X2254</my:sf424_ARPhone>

    <my:sf424_ARFax>(340) 772-2133</my:sf424_ARFax>

    <my:sf424_AREmail>bsmith@vienergy.org</my:sf424_AREmail>

    <my:sf424_DelinquencyExplanation>1432451435tertgs</my:sf424_DelinquencyExplanation>

    <my:field1 xsi:nil="true"></my:field1>

    <my:field2></my:field2>

    <my:field3>NaN</my:field3>

    <my:field4></my:field4>

    <my:field5 xsi:nil="true"></my:field5>

    <my:field6></my:field6>

    <my:sf424_DateReceive xsi:nil="true"></my:sf424_DateReceive>

    <my:field7 xsi:nil="true"></my:field7>

    </my:myFields>

    my xsd file is:

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

    <xs:schema id="NewDataSet" targetNamespace="http://schemas.microsoft.com/office/infopath/2003/myXSD" xmlns:mstns="http://schemas.microsoft.com/office/infopath/2003/myXSD" xmlns="http://schemas.microsoft.com/office/infopath/2003/myXSD" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified"

    xmlns:xml="http://www.w3.org/XML/1998/namespace">

    <xs:import namespace="http://www.w3.org/XML/1998/namespace" schemaLocation="sf424_test_app1.xsd" />

    <xs:element name="myFields" msdata:Prefix="my">

    <xs:complexType>

    <xs:sequence>

    <xs:element name="sf424_Revision" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="0" />

    <xs:element name="sf424_Preapplication" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="1" />

    <xs:element name="sf424_NewApplication" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="2" />

    <xs:element name="sf424_Application" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="3" />

    <xs:element name="sf424_ContinuationApplication" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="4" />

    <xs:element name="sf424_SelectOther" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="5" />

    <xs:element name="sf424_CorrApplication" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="6" />

    <xs:element name="sf424_RevisionApplication" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="7" />

    <xs:element name="sf424_ApplicantIdentifier" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="8" />

    <xs:element name="sf424_FederalEntityIdentifier" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="9" />

    <xs:element name="sf424_FederalAwardIdentifier" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="10" />

    <xs:element name="sf424_StateRecDate" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="11" />

    <xs:element name="sf424_StateApplicationIdentifier" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="12" />

    <xs:element name="sf424_LegalName" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="13" />

    <xs:element name="sf424_TaxpayerIdentification" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="14" />

    <xs:element name="sf424_OrgDuns" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="15" />

    <xs:element name="sf424_Street1" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="16" />

    <xs:element name="sf424_Street2" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="17" />

    <xs:element name="sf424_City" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="18" />

    <xs:element name="sf424_Country1" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="19" />

    <xs:element name="sf424_State" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="20" />

    <xs:element name="sf424_Province" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="21" />

    <xs:element name="sf424_Country2" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="22" />

    <xs:element name="sf424_Zip" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="23" />

    <xs:element name="sf424_DepartmentName" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="24" />

    <xs:element name="sf424_DivisionName" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="25" />

    <xs:element name="sf424_Prefix" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="26" />

    <xs:element name="sf424_FirstName" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="27" />

    <xs:element name="sf424_MiddleName" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="28" />

    <xs:element name="sf424_LastName" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="29" />

    <xs:element name="sf424_Suffix" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="30" />

    <xs:element name="sf424_Title" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="31" />

    <xs:element name="sf424_OrgAffiliation" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="32" />

    <xs:element name="sf424_FaxNumber" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="33" />

    <xs:element name="sf424_TelephoneNumber" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="34" />

    <xs:element name="sf424_Email" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="35" />

    <xs:element name="sf424_ApplicantType1" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="36" />

    <xs:element name="sf424_ApplicantType2" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="37" />

    <xs:element name="sf424_ApplicantType3" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="38" />

    <xs:element name="sf424_ApplicantTypeOther" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="39" />

    <xs:element name="sf424_FederalAgency" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="40" />

    <xs:element name="sf424_AssistanceNumber" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="41" />

    <xs:element name="sf424_CFDATitle" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="42" />

    <xs:element name="sf424_FundingOpportunityNumber" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="43" />

    <xs:element name="sf424_Title1" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="44" />

    <xs:element name="sf424_CompetitionIdentificationNumber" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="45" />

    <xs:element name="sf424_Title2" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="46" />

    <xs:element name="sf424_AreasAffected" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="47" />

    <xs:element name="sf424DescriptiveTitle" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="48" />

    <xs:element name="sf424_CongressionalApplicant" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="49" />

    <xs:element name="sf424_CongressionalProject" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="50" />

    <xs:element name="Text53" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="51" />

    <xs:element name="sf424_EndDate" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="52" />

    <xs:element name="sf424_StartDate" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="53" />

    <xs:element name="sf424_EstFundFederal" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="54" />

    <xs:element name="sf424_EstFundApplicant" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="55" />

    <xs:element name="sf424_EstFundState" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="56" />

    <xs:element name="sf424_EstFundLocal" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="57" />

    <xs:element name="sf424_EstFundOther" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="58" />

    <xs:element name="sf424_EstFundProgramIncome" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="59" />

    <xs:element name="sf424_EstFundTotal" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="60" />

    <xs:element name="sf424_ExecOrder12372Date" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="61" />

    <xs:element name="sf424_ExecOrder2372Rev" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="62" />

    <xs:element name="sf424_ExecOrder12372Subject" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="63" />

    <xs:element name="sf424_ExecOrder12372Not" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="64" />

    <xs:element name="sf424_ApplicantDelinquentExplain" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="65" />

    <xs:element name="sf424_ApplicantDelinquentYes" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="66" />

    <xs:element name="sf424_ApplicantDelinquentNo" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="67" />

    <xs:element name="sf424_Agree" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="68" />

    <xs:element name="sf424_ARPrefix" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="69" />

    <xs:element name="sf424_ARFirstName" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="70" />

    <xs:element name="sf424_ARMiddleName" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="71" />

    <xs:element name="sf424_ARLastName" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="72" />

    <xs:element name="sf424_ARSuffix" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="73" />

    <xs:element name="sf424_ARTitle" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="74" />

    <xs:element name="sf424_ARPhone" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="75" />

    <xs:element name="sf424_ARFax" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="76" />

    <xs:element name="sf424_AREmail" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="77" />

    <xs:element name="sf424_DelinquencyExplanation" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="78" />

    <xs:element name="field1" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="79" />

    <xs:element name="field2" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="80" />

    <xs:element name="field3" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="81" />

    <xs:element name="field4" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="82" />

    <xs:element name="field5" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="83" />

    <xs:element name="field6" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="84" />

    <xs:element name="sf424_DateReceive" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="85" />

    <xs:element name="field7" msdata:Prefix="my" type="xs:string" minOccurs="0" msdata:Ordinal="86" />

    </xs:sequence>

    <xs:attribute name="lang" msdata:Prefix="xml" type="xs:language" />

    </xs:complexType>

    </xs:element>

    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msdata:Prefix="my">

    <xs:complexType>

    <xs:choice minOccurs="0" maxOccurs="unbounded">

    <xs:element ref="myFields" />

    </xs:choice>

    </xs:complexType>

    </xs:element>

    </xs:schema>

  • April 5, 2010 at 7:43 pm

    #1146167

    Hi kyu,

    I wasn't able to insert it thru the SSIS XML source adapter.

    I think the lutz solution is very nice and worked great for me. Its very flexible and if u ask me, instead of wasting time trying to do it through SSIS, just use open row set tsql.

    Go through this thread and you'll be able to figure it out. In my opinion it works very nicely.

    Its actually xquery, Google it. Very nice.

    Thanks,

    S

    --
    :hehe:

  • April 5, 2010 at 11:32 pm

    #1146286

    I believe that there may be a 10,000 row limit with some xml imports / queries.

    pcd

  • April 6, 2010 at 5:12 am

    #1146389

    I just wonder why I can't see the data with this particular xml when I can with the other sample. One thing about the xml is that when you manually open the file, it tries to connect to SharePoint to get the template (xsn) information. I don't know if that's causing the issue or the namespace is causing the issue (I used xsd.exe to create the schema (xsd) as SSIS cannot handle multiple namespace. This is really frustrating!

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply