I worked on what ended up being a surprisingly interesting prototype to answer a question in social.msdn.microsoft.com.
I want to save whole XML message in sql table field which is with datatype XML(.).. how can i achieve this.
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"><![CDATA[</xsl:text> <xsl:copy-of select="/" /> <xsl:text disable-output-escaping="yes">]]></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!