Passing XML as a parameter to Oracle or SQL

In some cases we need to pass whole XML as a parameter to
stored procedure in Oracle or SQL

The issue is you cannot write XML as XML element
XML will be broken

The solution is pretty simple

1. Create a BizTalk project
2. Create new orchestration
3. Create new message InputXML and set type to System.Xml.XmlDocument
4. Create Receive shape assign InputXML to it as Message
5. Create Receive Port which will be assigned to file receive port in BizTalk applicatoin
6. Connect Receive Port with Receive shape
7. Create new Schema name it SQLParams

It should look like

XMLParam is the element which will hold our XML

8. Add new Message to orchestration name it Transformed, set type to SQLParams
9. Add variable Doc to orchestration , set type System.Xml.XmlDocument
10. Insert to orchestration Message Construct shape
11. Insert to Message Construct shape Message assignment shape name it Transformation
12. Edit Transformation , set expression


Doc.LoadXml(@"
<ns0:Root xmlns:ns0=""http://XMLParam.SQLParams"">
<Param1>Param1_0</Param1>
<Param2>Param2_0</Param2>
<XMLParam>XMLParam_0</XMLParam>
</ns0:Root>
");
Transformed = Doc;
Transformed.XMLParam = InputXML.OuterXml;

13. Create Send shape
14. Set Message to Transformed
15. Create Send port and connect it with Send shape
16. Deploy project
17. Create for testing 2 directories and bind them to orchestration
18. Put to receive folder xml file
<Params>
<r1>Param 1</r1>
<r2>Param 2</r2>
</Params>
19. Check output directory

In case if you are using input XML without namespace
do not forget to configure Receive pipeline as
AllowUnrecognizedMessage = true



Comments

Popular posts from this blog

How to poll database using WCF-SQL adapter

SQL query timeout from application but works fast from SSMS