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!

Advertisements

2 thoughts on “Save Whole XML File in SQL Table Field

    • Thanks for your comment. The context if this blog post, if I understand your question, is the other direction – putting XML into the database, not picking it up from the database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s