How do I use With XMLNamespaces to create custom name spaces in SQL?

Question

I want to be able to produce the following namesspaces and types for an XML root element

<BaseTransactionRequest xmlns="http://schemas.datacontract.org/2004/07/SomeCompany" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:type="AType">

Typically the first 2 (that is, not including i:type="AType") can be produced without issue (with some tradeoffs, when using custom namespaces we cant represent nulls using the xmlns:ni namespace etc) So, the latter type is problematic. For a referesher, the WITH XMLNAMESPACES fearure is used like below (FOR XML part omitted):

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as i, DEFAULT 'http://schemas.datacontract.org/2004/07/SomeCompany',

A solution to overcome was to write XML "literally" using string concatenation. But I believe and hope FOR XML and this can be used together.

EDIT: First cut was added in a real rush. Apologies. EDIT2: Dyslexic fix

Answer 1

Your question is not very clear... You might have a misconception about your i:type="AType". This is not a namespace (whatever a custom namespace is), but a normal attribute, named type living in your namespace i, which is declared at xmlns:i="blah".

Try this

WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as i
                   ,DEFAULT 'http://schemas.datacontract.org/2004/07/SomeCompany')
SELECT 'AType' AS [@i:type]
FOR XML PATH('BaseTransactionRequest');

The result is a self closing tag, declaring two namespaces and containing your attribute:

<BaseTransactionRequest xmlns="http://schemas.datacontract.org/2004/07/SomeCompany" 
                        xmlns:i="http://www.w3.org/2001/XMLSchema-instance" 
                        i:type="AType" />