December 20, 2016 at 7:01 pm
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[/url]
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!
February 6, 2017 at 1:00 pm
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!)
February 6, 2017 at 3:02 pm
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.
February 6, 2017 at 3:19 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply