Ytukyg

I am receiving a message from an SQL server in WCF-SQL adapter. In this message there is an XML node, which contains a fully formatted XML document as a string. What I need, is to extract only this document, ignoring the rest of the body, so that it can be processed further by a pipeline.

I've tried a bunch of xPath expressions in the "body path expression" input field in the config settings on the adapter, but none of them seems to work the way i expect.

Some xPath strings I've tried:

    /Polling/PolledData[1]/*[namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data' and local-name()='DataSet'][1]/*[namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1' and local-name()='diffgram'][1]/*[namespace-uri()='' and local-name()='NewDataSet'][1]/*[namespace-uri()='' and local-name()='NewTable'][1]/*[namespace-uri()='' and local-name()='msgbody'][1]



    /*[local-name()='Polling']/*[local-name()='PolledData']/*[local-name()='DataSet']/*[local-name()='diffgram']/*[local-name()='NewDataSet']/*[local-name()='NewTable']/*[local-name()='msgbody']



    /Polling/PolledData/DataSet/diffgr:diffgram/NewDataSet/NewTable/msgbody



    //*[msgbody]/text()

The body of the XML document I receive is structured like so, with the XML node I am trying to extract content from at the end:

<Polling xmlns="http://schemas.microsoft.com/Sql/2008/05/Polling/">

    <PolledData>

        <DataSet xmlns="http://schemas.datacontract.org/2004/07/System.Data">

            <xs:schema id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

                <xs:element msdata:IsDataSet="true" name="NewDataSet">

                    <xs:complexType>

                        <xs:sequence>

                            <xs:element minOccurs="0" maxOccurs="unbounded" name="NewTable">

                                <xs:complexType>

                                    <xs:sequence>

                                        <xs:element minOccurs="0" name="conversationID" type="xs:string"/>

                                        <xs:element minOccurs="0" name="hostUTC" type="xs:dateTime"/>

                                        <xs:element minOccurs="0" name="msgType" type="xs:string"/>

                                        <xs:element minOccurs="0" name="acknowledgment" type="xs:string"/>

                                        <xs:element minOccurs="0" name="sendLog" type="xs:string"/>

                                        <xs:element minOccurs="0" name="msgFormat" type="xs:string"/>

                                        <xs:element minOccurs="0" name="msgbody" type="xs:string"/>

                                        <xs:element minOccurs="0" name="fromID" type="xs:string"/>

                                        <xs:element minOccurs="0" name="toID" type="xs:string"/>

                                    </xs:sequence>

                                </xs:complexType>

                            </xs:element>

                        </xs:sequence>

                    </xs:complexType>

                </xs:element>

            </xs:schema>

            <diffgr:diffgram xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

                <NewDataSet xmlns="">

                    <NewTable>

                        <conversationID>b4327577-14d1-478d-9e22-027683c0c5f9</conversationID>

                        <hostUTC>2018-11-19T13:17:07.03Z</hostUTC>

                        <msgType>INVOIC</msgType>

                        <msgFormat>oioUBL</msgFormat>

                        <msgbody>&lt;Invoice xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 

I'm unsure how to properly write the correct xPath to this node. Reading the documention on MS docs seems to indicate that I need to use the local-name syntax. I think my problem lies in the use of namespaces in the received XML, but I don't know how to include these in the xPath.

Your first XPath was nearly right, except you were missing the namespaces from it for the first two nodes.

Also the [1]'s in your first your XPath examples are necessary unless there are multiple nodes and you wish to select the first one.

The correct Xpath would be

/*[local-name()='Polling' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Polling/']

/*[local-name()='PolledData' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Polling/']

/*[local-name()='DataSet' and namespace-uri()='http://schemas.datacontract.org/2004/07/System.Data']

/*[local-name()='diffgram' and namespace-uri()='urn:schemas-microsoft-com:xml-diffgram-v1']

/*[local-name()='NewDataSet' and namespace-uri()='']

/*[local-name()='NewTable' and namespace-uri()='']

/*[local-name()='msgbody' and namespace-uri()='']

But I you might still have an issue as it looks like the rest of your payload has been escaped, e.g. the < is a <