October 25, 2008 at 10:24 pm
I get the concept of importing from XML to an SQL table. Created the table from xsd file - having to do some data conversion. Is there an authoritative source that shows which data types are best on the SQL side? I end up with BIGINT a lot where I think INT might should do. I have had to convert from D_UI8 to int to get the import to work -
There are mappings for about 10 tables in the single xml file. I suppose that I have to handle each one manually initially to get the tables created. And a separate error table for each one, as well? This will be a recurring, daily update from files of the same name.
Most of what I see for dealing with XML is rather basic. Does anyone know of a resource that discusses it more fully?
Happens to be my first SSIS package, too. I understand the basics and can get a lot of it to work. But a more comprehensive look at the kinds of issues and good practices with XML would be helpful.
October 26, 2008 at 11:17 am
When I try to create a table from the XSD I figured that there would be no import issues ... but no, I still have to use data type converter. What's up with that?
Also - the XSD defines some datatypes as "xs:string" with a length of 6, or any other length. The SQL table is nvarchar(255). I know for a fact that the original, before getting exported to XML, was varchar2(6) in Oracle. I will not need the overhead of Unicode, nor space for the extra 249 characters.
Or take a decimal. This:
xs:simpleType name="PercentType"
xs:restriction base="xs:decimal"
xs:totalDigits value="6"/
xs:fractionDigits value="2"/
/xs:restriction
/xs:simpleType
is showing up in the Create Table sql as "decimal (29,10)" ... my reading of the XML is Precision of 6 and scale of 2 resulting in either "decimal(6,2)" or "numeric(6,2)" on the SQL side.
I have 27 tables to import from XML, and there are lots of columns in some of the tables. I didn't anticipate that there would be hourse of manual tweak time at the initial stages. Is there something I am missing in terms of getting the XML data types read properly?
I'm looking at
1) copying, pasting and then heavily editing the Create Table statements from XSD as one alternative. I have the Create Table statements in PL/SQL for Oracle ... I could
2) do a bunch of find/replace actions there and then create tables in SQL Server. OR, as I have done, I could
3) run those statements in Oracle, and then use SSIS to pull the empty tables over to SQL Server. And then try to import the XML. That leaves me with many columns per table to convert from a Unicode DT to string so the content can go into a varchar column.
4) Trying to create the tables from XSD has the issues recorded above.
Every alternative I have reviewed requires loads of manual tweaking. I need, in the end, to do an initial load of historical data, and then add to those tables daily via the same SSIS mechanism.
Help! 🙂
October 27, 2008 at 4:06 pm
Somebody's got to have an answer!
Besides the datatype conversion issues described above, another question.
I ran a validator on the xml file against its xsd definition, and it got a passing grade.
The xml file starts with
employees
employee empid="blah" Unitcode="blum"
gendata this="that" thum="threw"
budgdata perc="20" FPT="F"
PayrollData prCode="scrum"
more entities ... /
/PayrollData
/employee
/employees
So ... there are 27 different elements that become database tables within one "employee" ... I can get each of them to load in individually, and also another table called "employee" which holds the empid, Unitcode and prCode .... However, those all have to go into each of the other 27 tables because they are what identify the employee in all cases, and the payroll number in most cases. But how do I get the data from these parent elements into each of the children elements?
Is this where a merge task comes into play? I can see that ... but how do I know that it is wiring the right employee id to the right child elements? ... because that's the xml record that is being read at the time, so it has to? I'll give it a try.
Am hoping for some experienced guidance. Anyone?
October 28, 2008 at 12:05 pm
ok, let's try a simple single question. Does SSIS, when pulling from an XML source, create a "pseudo" column of sorts as an identifier?
I'm seeing such a column which is the element name followed by _Id ... I'm merging two XML sources (from the same XML/XSD), sorting first, and this column appears. I join the two datasets on this column (it's the only one in both) and it looks like the data is being wired up correctly ... but the employee_Id doesn't exist either by that name, or by the value it passes, anywhere in the XML/XSD. So that's just internal to SSIS as a row identifier? I can join on it, but don't have to pull it in to the table.
Am I getting it right?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply