SSIS Top Node of XML Schema

Question

SSIS Top Node of XML Schema

SQLRNNR

SSC Guru

Points: 281334
More actions
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

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

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

LutzM SSC Guru Points: 107049 More actions · Answer 3

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]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

LutzM SSC Guru Points: 107049 More actions · Answer 5

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]