Using content from a single XML node as the XML document, while promoting the XML nodes on the same level

I am receiving an XML document, which contains another XML document inside on of its nodes as seen in the structure below:

<NewTable>
    <conversationID>f5296f48-90b4-4370-8f16-0115a105c161</conversationID>
    <hostUTC>2018-11-20T16:29:36.04Z</hostUTC>
    <msgType>INVOIC</msgType>
    <msgFormat>oioUBL</msgFormat>
    <msgbody>OTHER XML DOCUMENT...</msgbody>
    <fromID>GLN:5790012328619</fromID>
    <toID>KMDoioUBL</toID>
</NewTable>

I thought about using an Envelope schema, but in my case, I can't just use the tag as a child to the NewTable node. I don't need a schema for the XML in the msgbody tag, it should just be sent as a passthrough. What I need, is to promote some of the other XML nodes, such as msgType, so that they can be used to filter the document in Biztalk, while only sending the content in the msgbody tag.

Is this scenario possible using the Envelope schema in Biztalk?

Solution

I take it this is related to your question Extracting XML document in XML node from WCF-SQL message using body path expression

The first thing you probably need to do is to removed any Min Occurs or Max Occurs from any of the nodes so that they only occur 1. Then you can promote the properties you want to preserve in your message context.

The next part would be to extract the msgbody part, in your previous question the answer was to use a XPath to extract the body part in the Adapter, but then you will lose those headers before it goes through the XML dissasembler, so rather than that you will want to create a map to extract it for you instead.

Just create a map that has the input and output set to your schema, create a link from msgbody to msgbody. Then right click on the map in Solution Explorer and click Validate map. This will generate a XSLT file. Copy that XSLT file into your project directory for the map and add it as an existing item to the project. Click on the grid of your map and set the Custom XSLT path to your XSLT.

From your example XML file in this question your XSLT will look like

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/NewTable" />
  </xsl:template>
  <xsl:template match="/NewTable">
    <NewTable>
      <msgbody>
        <xsl:value-of select="msgbody/text()" />
      </msgbody>
    </NewTable>
  </xsl:template>
</xsl:stylesheet>

But as you only want the value of the body you will want to edit the XSLT to remove the <NewTable>,<msbody>,</msgbody> & </NewTable> and any other output nodes you don't want.

And finally, if you XML is escaped as per your previous question, you will want to add disable-output-escaping="yes" to the select as below and as per How to unescape XML characters with help of XSLT?

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/NewTable" />
  </xsl:template>
  <xsl:template match="/NewTable">
        <xsl:value-of select="msgbody/text()" disable-output-escaping="yes"/>
  </xsl:template>
</xsl:stylesheet>

Note: Testing the map in Visual Studio you will have to disable output validation as of course it does not match the schema. However you could create a schema for that XML.