Post reply

SSIS Top Node of XML Schema

  • January 14, 2011 at 3:54 pm

    #146842

    I am trying to import XML docs into a database through SSIS. I have the package setup and it reads all of the nodes except the top most (and tying piece) from the XSD. I have recreated an XSD, but still no luck.

    Here is what the XSD looks like.

    Any ideas how to get the RepNo string to populate so I can use it downstream (the field is in every XML doc. XSD recreated from XML doc using Visual Studio as well as BIDS.)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 14, 2011 at 4:14 pm

    #1273843

    Can you post just a small sample of the xml doc itself and also the xsd?

    Out of interest, have you tried using something like IE to view the xml but add the reference to the xsd so it validates it? I guess - have you used any tools to validate the XML doc against the xsd?

    Steve.

  • January 14, 2011 at 4:21 pm

    #1273844

    Yes the xml validates and I have tried. SSIS reads the xml files just fine. I can get the data loaded - but I really can't tie the data together between the nodes without that Repno field.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 14, 2011 at 4:53 pm

    #1273850

    It seems like there is a known issue when trying to get the attributes out of the outer most node.

    In this blog in a comment they mention "Your best bet is to massage the data bit with XSLT before processing.", which is described here.

    If that's not an option you might consider using a SQL task to get the top attributes.

    Something like

    ;WITH xmlnamespaces(DEFAULT 'urn:reuterscompanycontent:referenceinformation03')

    SELECT

    c.value ('@Major[1]','varchar(10)') a,

    c.value ('@Minor[1]','varchar(10)') b,

    c.value ('@Revision[1]','varchar(10)') d

    FROM @xml.nodes('ReferenceInformation') T(c)

    Then merge the data. Kinda dirty approach though... But I couldn't find any easier option than the two I mentioned. Maybe shredding the XML data using XQuery instead of SSIS could be an option, too...


    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]

  • January 14, 2011 at 5:50 pm

    #1273852

    Thanks. XML is not my first language, nor my second, third ....tenth, etc etc.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 15, 2011 at 2:37 am

    #1273882

    If I can be of any help, lemme know...


    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]

Viewing 6 posts - 1 through 5 (of 5 total)

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