XPath to return default value if node not present

I had to put this into use today in a BizTalk map (inline XSLT). Since this is in BizTalk, I was limited to XPath 1.0 as a solution.

http://stackoverflow.com/questions/4489976/xpath-to-return-default-value-if-node-not-present

This is the problem/solution from StackOverflow:

Problem

Say I have a pair of XML documents

<Foo>
<Bar/>
<Baz>mystring</Baz>
</Foo>

and

<Foo>
<Bar/>
</Foo>

I want an XPath (Version 1.0 only) that returns “mystring” for the first document and “not-found” for the second.

Solution

In XPath 1.0, use:

concat(/Foo/Baz, substring(‘not-found’, 1 div not(/Foo/Baz)))

If you want to handle the posible empty Baz element, use:

concat(/Foo/Baz, substring(‘not-found’, 1 div not(/Foo/Baz[node()])))

With this input:

<Foo>
<Baz/>
</Foo>

Result: not-found string data type.

This worked beautifully!

Save Whole XML File in SQL Table Field

I worked on what ended up being a surprisingly interesting prototype to answer a question in social.msdn.microsoft.com.

Question:

 I want to save whole XML message in sql table field which is with datatype XML(.).. how can i achieve this.

Answer:

I did a prototype to do what you are asking. I actually haven’t inserted into the xml data type in SQL Server before, so I was curious if that added any complexity. I’ll just go through the mapping pieces and assume that you can easily find documentation on how to insert data into SQL Server using the Add Generated Items –> Consume Adapter Service function.

The interesting piece was writing the map. I used a simple PO schema. My sample table was XmlRepository with a ID column, RepositoryID, and the RawXml field.

The script is an inline XSLT script that is as follows:

<xsl:element name="ns3:RawXml">
<xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
<xsl:copy-of select="/" />
<xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>
</xsl:element>

 

The question right off is why the CDATA? Well, since the value of the RawXml element that needs to be inserted into SQL Server is XML data, it has to be wrapped in a CDATA tag. Otherwise, the XML data gets validated and will not properly make it to the SQL Server. You can try it and see what I mean. Don’t worry, the CDATA gets stripped before the XML gets written to the table.

The interesting pieces are the xsl:text elements, which manually create a CDATA wrapper for the XML that ends up in the RawXml field. Apparently, the normal Grid Property for creating CDATA output doesn’t work in conjunction with the xsl:copy-of element. That tripped me up for a while!

What the map outputs is a document that looks like the following:

<ns0:Insert xmlns:array="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo"
xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/XmlRepository">
	<ns0:Rows>
		<ns3:XmlRepository>
			<ns3:RawXml>
				<![CDATA[<ns0:PurchaseOrder xmlns:ns0="http://XmlRepositoryDemo.PurchaseOrder">
				  <PONumber>PONumber_0</PONumber>
				  <CustomerNumber>CustomerNumber_0</CustomerNumber>
				  <PODate>PODate_0</PODate>
				  <Items>
					<Item>
					  <ProductID>ProductID_0</ProductID>
					  <Quantity>Quantity_0</Quantity>
					  <Price>Price_0</Price>
					</Item>
				  </Items>
				</ns0:PurchaseOrder>]]>
			</ns3:RawXml>
		</ns3:XmlRepository>
	</ns0:Rows>
</ns0:Insert>

 

The map can be called from a simple Orchestration, or even a messaging-only scenario, and sent across the wire in a request/reply as you would for normally sending data to SQL Server.

I hope this helps someone. It ended up being more than I expected!

XSLT String Padding

I had the need to pad values in a xsl template from a BizTalk map. These helpful call templates did the trick:

http://www.dpawson.co.uk/xsl/sect2/padding.html

<xsl:template name=”prepend-pad”>
<!– recursive template to right justify and prepend–>
<!– the value with whatever padChar is passed in   –>
<xsl:param name=”padChar”/>
<xsl:param name=”padVar”/>
<xsl:param name=”length”/>
<xsl:choose>
<xsl:when test=”string-length($padVar) &lt; $length”>
<xsl:call-template name=”prepend-pad”>
<xsl:with-param name=”padChar” select=”$padChar”/>
<xsl:with-param name=”padVar” select=”concat($padChar,$padVar)”/>
<xsl:with-param name=”length” select=”$length”/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select=”substring($padVar,string-length($padVar) –
$length + 1)”/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

<xsl:template name=”append-pad”>
<!– recursive template to left justify and append  –>
<!– the value with whatever padChar is passed in   –>
<xsl:param name=”padChar”/>
<xsl:param name=”padVar”/>
<xsl:param name=”length”/>
<xsl:choose>
<xsl:when test=”string-length($padVar) &lt; $length”>
<xsl:call-template name=”append-pad”>
<xsl:with-param name=”padChar” select=”$padChar”/>
<xsl:with-param name=”padVar” select=”concat($padVar,$padChar)”/>
<xsl:with-param name=”length” select=”$length”/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select=”substring($padVar,1,$length)”/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

BizTalk Map Documenter for 2010

I have a project with BizTalk 2010 and really want to use the BizTalk Map Documenter for the maps. I pointed it to my maps and the output did not show the content from Constant Functoids or Links into Functoids.

I looked through the XSLT file and discovered a couple of tweaks that got it all working.

Basically, everywhere in the XSLT where it references “@Type = ‘Link'” or “@Type = ‘Constant'” had to be changed to “@Type = ‘link'” and “@Type = ‘constant'”.

XLST is a case-sensitive language. Apparently in BizTalk 2010 the Type for Links and Constants in the map-generated code has all lower-case “link” and “constant”.

Anyway, I am glad to have found an easy solution. It sure beats having to put all that documentation together by hand! There will be lots of maps by the end of this project, so I am happy to have an automated tool to create useful docs.

BizTalk Map Documentor

I’ve had some great success using the BizTalk Map Documentor:

http://biztalkmapdoc.codeplex.com/releases/view/31451

It is a XSLT file that creates a nice HTML representation of maps including links, functoids and constants.

When coupling it with the Microsoft Command Line Transformation Utility and a batch file to loop through all of the contents of a directory, I can easily keep map documentation up-to-date. It’s a great way to communicate mappings to non-Biztalk folks. It is still technical documentation, but I’ve gotten good feedback from non-technical folks.

Here is the batch file I use:

for %%X in (C:\mymappath\*.btm) do c:\msxsl.exe “%%X” c:\BizTalkMapDocumenterHTML.xslt -O “%%X.html”

msxsl.exe and the BizTalkMapDocumentorHTML.xslt must be in the same directory.

How to remove xmlns attributes

How to remove xmlns attributes in html out put via copy-of

Yippie friggin doo da! I found this xsl script that strips namespaces and prefixes out of an xml document. This is going to be very useful in some Biztalk 2004 transformations that I doing.

This is what I ended up with:

<?xml version=”1.0″ encoding=”UTF-8″ ?>
<xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”&gt;
<xsl:template match=”*”>
<!– remove element prefix (if any) –>
<xsl:element name=”{local-name()}”>
<!– process attributes –>
<xsl:for-each select=”@*”>
<!– remove attribute prefix (if any) –>
<xsl:attribute name=”{local-name()}”>
<xsl:value-of select=”.” />
</xsl:attribute>
</xsl:for-each>
<xsl:apply-templates />
</xsl:element>
</xsl:template>
</xsl:stylesheet>