SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import XML file using SSIS


Import XML file using SSIS

Author
Message
Scott Thornton-407727
Scott Thornton-407727
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1540 Visits: 269
Hi,

Thought I'd do some end-of-year learning and import an XML document using SSIS.

I am using this guide as a tutorial

As there is no .xsd supplied with the file, I used the "Generate XSD" button on the XML Source Editor Wizard.

When I view the columns with the wizard, each attribute has its own occurrence in the Output name field, rather than a single item "Data" with multiple "Available External Columns". It looks like each element is its own "table", rather than a single table containing elements.

The XML is straight forward I think, and looks like:

e.g.
<?xml version="1.0" encoding="windows-1252"?>
<!--MBS Data - Created 21/12/2016 9:53:24-->
<MBS_XML>
<Data>
<ItemNum>3</ItemNum>
<SubItemNum></SubItemNum>
<ItemStartDate>01.12.1989</ItemStartDate>
<ItemEndDate></ItemEndDate>
<Category>1</Category>
....
</Data>
<Data>
<ItemNum>3</ItemNum>
<SubItemNum></SubItemNum>
<ItemStartDate>01.12.1989</ItemStartDate>
<ItemEndDate></ItemEndDate>
<Category>1</Category>
....
</Data>
</MBS_XML>

I have attached the generated xsd as a txt attachment, and an image of the XML Source editor displaying the fields inside of the Output name.

I have next to no knowledge of XML so I am very much a rookie!

I am probably missing something obvious, but if anyone could point out what I have done incorrectly, I would appreciate it!
Attachments
xsd.txt (39 views, 3.00 KB)
editor.png (31 views, 52.00 KB)
pisorsisaac
pisorsisaac
SSC Eights!
SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

Group: General Forum Members
Points: 841 Visits: 92
Scott, did you ever get a solution to this somewhere?
I was very curious on your post, I am new to the forum and had some XML trouble too. Given that so many people have given me the impression that XML is the best thing since sliced bread and any competent SQL server developer simply must know it, I assumed the forum would burst with joyful rivers of advice, but rather I don't quite get that feel (other than "don't use SSIS for XML related tasks", and it seems your question got zero responses, unless my newness to the forum is impacting my ability to see replies.......

For me everything is working "ok" on the import, except values in ID attributes aren't coming through correctly. They're spontaneously generating other whole number values rather than the "1" and "2" values I see in the XML file, which I would depend on to join the data which is going into, (of course), a different sql table per-"layer" of XML elements.

(I normally wouldn't muddle up a forum by poking around for additional help on other people's questions, but since there's little activity on either one, we might as well create some here!)
Scott Thornton-407727
Scott Thornton-407727
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1540 Visits: 269
HI Mate,

No, I never found a solution to the problem I was experiencing, I do need to look into it again.

I was starting to think I might need to update my SQL Data Tools ( thinking the Import Wizard GUI might be the problem). When it runs, it opens the 2010 shell.. surely there has been update of the tools...

With regard to your problem, all I can think of is that perhaps the field you think you are inserting into contains an Identity data type ( auto incrementing ).

Good Luck.
pisorsisaac
pisorsisaac
SSC Eights!
SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

Group: General Forum Members
Points: 841 Visits: 92
Yes, that was my thought too (as to my problem), but such is not the case. However, over today's readings and musings, I've concluded that OPENROWSET[BULK] and placing the data into an actual XML field, then using other T-SQL functions to query it (and insert it elsewhere) is probably worth learning, and not even trying the SSIS [data flow] load again.

Thanks for the reply, have a fantastic rest of the day/night.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search