How to remove blank namespace when retrieving data using WCF-SQL adapter with FOR XML clause stored procedure
Problem Description
==================
When using BizTalk WCF-SQL adapter to retrieve data from
SQL, there is a known issue that a blank namespace may be generated when a FOR
XML clause stored procedure is used. For example, the empty xmlns="" on FTP_KC_SERIAL_NO_HEAD in the
following XML instance.
<?xml version="1.0" encoding="utf-8"
?>
- <Root xmlns="https://TestEDI.SP_GetDataFromFTPKCSerialNo">
- <FTP_KC_SERIAL_NO_HEAD xmlns="" >
<INV_NO>30111112173</INV_NO>
<ImportDate>8/16/2011 3:05:43
PM</ImportDate>
<Status>processing</Status>
<SendDate>8/16/2011 3:35:21
PM</SendDate>
<FROM_DUNS>12490306</FROM_DUNS>
Problem Analysis
================
There are various of approaches to resolve the issue, a
simple workaround may be to set “FORM = UNQUALIFIED” on the message schema to
ignore it.
However sometimes this kind of blank namespaces will cause
errors within map. In this case, you may consider writing a simple pipeline
component to completely remove it from the XML message body.
Problem Resolution
=================
Sample code snippet of pipeline component to remove blank
xml namespaces.
#region IComponent Members
/// <summary>
/// This method Executes
the pipeline component
/// </summary>
/// <param name="pContext">pipelinecontext Interface</param>
/// <param name="pInMsg">the incoming XLang Message on which we have to operate</param>
/// <returns></returns>
public IBaseMessage Execute(IPipelineContext
pContext, IBaseMessage pInMsg)
{
//Validate parameters
if (pContext == null)
throw new ArgumentNullException("pContext");
if (pInMsg == null)
throw new ArgumentNullException("pInMsg");
this.pipelineContext = pContext;
this.baseMessage = pInMsg;
Stream outStream = null;
string partName;
for (int i = 0;
i < baseMessage.PartCount; i++)
{
partName = null;
IBaseMessagePart part = baseMessage.GetPartByIndex(i, out
partName);
RemoveBlankNamespace(part.GetOriginalDataStream(), out
outStream);
outStream.Seek(0, SeekOrigin.Begin);
part.Data = outStream;
}
return baseMessage;
}
#endregion
#region Private-Methods
/// <summary>
/// This method remove
blank xml namespace attribute: xmlns="" from input message body
stream.
/// </summary>
/// <param name="xmlData">the actual stream of the data needs to be manipulated</param>
/// <param name="modifiedXmlData">the modifiedStream is passed as a out parameter</param>
/// <returns></returns>
private void
RemoveBlankNamespace(Stream xmlData, out Stream
modifiedXmlData)
{
modifiedXmlData = new MemoryStream();
StreamReader reader = new StreamReader(xmlData);
String messageBody = reader.ReadToEnd();
messageBody = messageBody.Replace("
xmlns=\"\"","");
byte[] messageBytes = System.Text.Encoding.UTF8.GetBytes(messageBody);
modifiedXmlData.Write(messageBytes,
0, messageBytes.Length);
}
#endregion
More Information
=================
A relevant forum thread:
Namespace appear in the child node when using FOR XML output
in BizTalk server 2009 and the map fails