Tagged: SQL Server


Denormalise (flatten) XML using Xquery

ETL software have ways to interpret XML files or structures that vary in maturity, but recently I had to find another way to import XML structures that are received as blobs using (transact) SQL only. Because the upstream patterns (Staging to Integration) can easily handle denormalised source data sets the easiest way was to cast each blob as the XML file it is anyway and completely denormalise it (‘flatten’, more rows…). This is opposed to normalising...


Hashing in SQL Server and Oracle for the same output

Thanks go out to my colleagues for testing this out further, but we were testing out a way to get Oracle to generate hash values with the same output as the default SQL Server MD5 hashbytes function. In previous projects I used to rely on the DBMS_CRYPTO package in Oracle but this requires additional privileges. So if there is a some red tape to cut through the following code using the DBMS_OBFUSCATION_TOOLKIT works as well, and you...


User Defined Properties for ETL Automation – the final piece of the puzzle

At the end of the development efforts to support true Model Driven Design there are some elements you just don’t want to store in another metadata table. But somehow you need specific information which isn’t available in the Data Dictionary either (or directly derivable that way). In my case I wanted to label attributes within a Dimensional Model to behave following specific paradigms for presenting history – Type0, Type1 or Type2 – and essentially use this information...