Wednesday, June 6, 2018

OpenXML Queries with Namespaces.

OpenXML is a convenient way to extract data in XML inside SQL Server however there are a number of gotchas, particularly if your file contains namespaces.

Namespaces attempt to envelope certain field names so duplicate element names are distinct within their own namespace. Why anyone would use the same element name for two different elements in an XML document is beyond me but they do exists and you must handle them in OpenXML otherwise the function won’t work.

This is what my XML document looked like:


The first thing that has to happen is that all the namespaces need to be defined to the sp_xml_preparedocument procedure call. That includes the root and child namespace declarations.

The namespace definitions are passed to the sp_xml_preparedocument procedure as the third parameter. It’s not important to use the same element name (Original:message, Definition:root) because you are just defining the namespaces to OpenXML.

It IS important to define a prefix for the namespace for ALL the namespaces in your document. In my case I had a namespace in the [message] element that did not have an alias so I gave that an alias of :hm. I also had a namespace defined in the [fxRates] element. I defined that also and gave it an alias of :mr. You don’t need to alias the schemaLocation. (@XML is a variable of type XML containing the xml document).


Once the aliases are defined you can then reference the elements in the file USING the namespace alias. That is you MUST use the namespace to define the elements. You don’t use the namespace alias for attributes.

For the header, that looks like:

SELECT DomainName,
       msgVersion,
       SubdomainName,
       Identifier,
       IdentifierVersion,
       SourceName,
       MessageId,
  FROM OPENXML(@hDoc, '//hm:message/hm:header/hm:meta', 1)
  WITH (
          DomainName varchar(100) 'hm:domain/hm:name',
          msgVersion varchar(100) 'hm:domain/hm:msgVersion',
          SubdomainName varchar(100) 'hm:domain/hm:subdomain/hm:name',
          Identifier varchar(200) 'hm:identifier/hm:id',
          IdentifierVersion varchar(20) 'hm:identifier/hm:version',
          SourceName varchar(100) 'hm:source',
          MessageId varchar(100) 'hm:messageId',
       )

To get the market rates out, there is an additional gotcha. You must OpenXML using the appropriate alias for the name space AND go down to the lowest level repeating element, to retrieve the rows at that level.

I initially tried:

-- CLOSE BUT NO CIGAR
SELECT PrimaryCurrency,
       SecondaryCurrency,
       RateDate,
       ExchangeRate,
       DaysAhead,
       TenorPeriod,
       Period
  -- notice the use of hm: and mr: aliases in this statement
FROM OPENXML(@hDoc, '//hm:message/hm:payload/mr:fxRates', 2)  WITH ( PrimaryCurrency varchar(6) ' mr:fxRate/mr:currencies/mr:primaryCurrency', SecondaryCurrency varchar(6) 'mr:fxRate/mr:currencies/mr:secondaryCurrency', RateDate date 'mr:fxRate/mr:date', ExchangeRate float '/mr:fxRate/mr:exchangeRate/mr:rate', DaysAhead int '/mr:fxRate/mr:exchangeRate/mr:daysAhead', TenorPeriod varchar(3) '/mr:fxRate/mr:exchangeRate/mr:tenorPeriod', Period float '/mr:fxRate/mr:exchangeRate/mr:period' )


But while fxRates is the root of the repeating element, the actual repeating element in the section is [exchangeRate]. The correct statement is below. Because you need to drill down to the lowest level you must go back up to get common elements.

-- CORRECT STATEMENTSELECT PrimaryCurrency,
       SecondaryCurrency,
       RateDate,
       ExchangeRate,
       DaysAhead,
       TenorPeriod,
       Period
  -- notice the use of hm: and mr: aliases in this statement AND the level
  FROM OPENXML(@hDoc, '//hm:message/hm:payload/mr:fxRates/mr:fxRate/mr:exchangeRate', 2)  
  WITH (
          -- go back up to get the currencies
          PrimaryCurrency varchar(6) '../mr:currencies/mr:primaryCurrency',   
          SecondaryCurrency varchar(6) '../mr:currencies/mr:secondaryCurrency',
          -- go back up to get the rate date
          RateDate date '../mr:date',             
          ExchangeRate float 'mr:rate',
          DaysAhead int 'mr:daysAhead',
          TenorPeriod varchar(3) 'mr:tenorPeriod',
          Period float 'mr:period'
       )

In brief:

  1. You must define all namespaces with aliases in the sp_xml_preparedocument statement
  2. You must use the correct alias on all elements in the open xml reference.


And don't forget to close the handle!
exec sp_xml_removedocument @hDoc;

Saturday, May 28, 2016

The Miracle Of SQL

SQL or the Structured Query Language, first appeared in 1974. The two blokes credited with writing it, Donald D. Chamberlin and Raymond F. Boyce got that remit so right that it stands today as a tower of software genius. It has been around for 42 years and is still the undisputed defacto standard for managing a relational data store. 42 years and counting! In the software industry that is eternal.

One of the precious few good things to ever come out of IBM, it went ANSI in 1986, 30 years ago and since then has only seen minor revisions. Of course there are issues, forgetting to specifically define the date, time and datetime standards arguably being one, but forgivable considering the enormity of the gift. Since 1986 there have been many diversions from the definition by many different vendors but the core is largely unchanged.

To Don and Ray, Thankyou.

Friday, May 20, 2016

Solving the unique name on delete issue

A common requirement for data management is to have a table that contains a name that must be unique within a table. If rows cannot be physically deleted from the table, a status field is usually used to indicate that the record has been deleted. If another row is added with the same name, an error will be generated because the new row fails the unique constraint on the index.

To solve this issue, add another field called (say) DeletionId that defaults to zero and populate that field with the id of the record when the record is flagged as deleted. Include this field in the unique index.

For example, assume the following table

create table MyTable

   MyTableId int identity(1,1) not null
  ,MyTableName varchar(100) not null 

  ,StatusId smallint not null 
  ,DeletionId int not null constraint DF_MyTable_DeletionId default 0 
  ,etc... 
  ,constraint PK_MyTable primary key (MyTableId) 
  ,constraint UK_MyTable_MyTableName Unique (MyTableName, DeletionId) 


To flag as deleted (if 2 means deleted and 5 is the id of the record):

update MyTableName set StatusId = 2, DeletionFlag = MyTableId where MyTableId = 5

By using the DeletionId it is possible to have many deleted records in the table with the same name but only one record in the table that is not deleted with the name. That is, the undeleted name is unique.

Tuesday, May 3, 2016

Oracle Linked Server Issues

If you get the error message below while trying to connect to an Oracle linked server:

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider “OraOLEDB.Oracle” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider “OraOLEDB.Oracle” for linked server “(null)”.


...you may need to set the "Allow Inprocess" setting on the provider to enabled.

To Fix:

  1. Go to Server Objects\Linked Servers\Providers folder in SQL Server Management Studio on the Sql Server that has the linked server, locate the Oracle Provider (OraOLEDB.Oracle) and right click to Properties.
  2. Ensure Allow Inprocss is checked
  3. Recreate your linked server.
Also, make sure you have restarted the SQL Server service after you have installed the Oracle OleDB driver.

Look here for details.

Saturday, April 16, 2016

Classloader Notes

Notes about Java Class Loaders

  1. The system class loader loads classes where it can find them. This is typically defined by the classpath setting at the environment level. A custom class loader can extend this by sub classing the default class loader and by allowing the user to specify a series of locations or jar files that can be searched to locate a class. This provides more flexibility than the standard class loader.
  2. Most custom class loaders will provide a cache so that searching for a class is faster. The cache is typically a HashMap with the name of the class being the key. The constructed class is stored against the key name. To construct a class is relatively expensive and is performed by the defineClass() method. This method takes an array of bytes and returns a constructed class. The constructed class is registered with the JVM, which means you can't call defineClass() on the same class name by the same classLoader twice or you will get a duplicate class definition exception. There is no other mechanism to return a constructed class from an array of bytes. The array of bytes is easy enough to read from the file, the construction of the class is done at the JVM level. That is so that the checks for linkages, security and other class related activities can be achieved.
  3. A class "signature" is made up of the classLoader that loaded the class and the actual class name. That means is you load a class from a custom class loader it will have a different class "signature" to the class "signature" generated by the system class loader or by another custom class loader instance. The only way I've found to dynamically refresh a class is to create an instance of a custom class loader, load the class, change the class file by modifying the code and recompiling, copying the compiled class file to a place where the custom class loader can find it, recreate the instance of the class loader and then reload the class. In this way, the class "signature" of the second instance of the class will be different to the "signature" of the first instance of the class as they used a different instantiation of the class loader. 
  4. You can't cast an object across class loaders. That means if you load an object into a custom class loader it will NOT be the same reference to the class from the original class loader. If you consider the previous point, each of these classes will have a different "signature" therefor would not be compatible. That is, assuming all classes are visible, this is not allowed:
// assume we are in classLoaderA which may be the 
// system class loader.
// the TestClass reference is therefore relative to 

// classLoaderA
TestClass test;


// create a new class loader
MyClassLoader classLoaderB = new MyClassLoader();


// the class that is returned from the following is 

// relative to classLoaderB but the cast class is 
// relative to classLoaderA. 
// This will cause a class cast exception
test = (TestClass) classLoaderB.loadClass("TestClass");


If you need to use classes across classLoaders you have to use interfaces. 

Thursday, December 3, 2015

Grant permission to create a table in SQL Server. No DDLAdmin

You can set the ability to create a table in a SQL Server database with a grant statement. See here
[Grant Syntax]

You don't have to assign ddladmin privileges.

Thursday, June 11, 2015

Viewing DDL in SQL Server

To provide a read only view of the list and content of the DDL elements (procedures, functions etc) in SQL Server without granting ddladmin use

GRANT VIEW DEFINITION ON SCHEMA::dbo TO [domain\user]