Invoke Web Services Object – Workaround for “Unexpected” XML Output

  • Article

No video in this post, more of a traditional “Tip and/or Trick” :)

You may have noticed that when using the “Invoke Web Services” object, the XML output generated has a bunch of “&lt;” and “&gt;” values instead of the expected “<” and “>”. It really depends on the Web Service, but if you see these results, you will have a difficult time using the "Query XML” object to parse the resulting XML, as it is expecting “<” and “<”, NOT “&lt;” and “&gt;”.

Well you are in a bit of luck, I have a pretty easy and clean workaround…

The workflow ends up looking like this (with the addition of a “Query Database” object):

image

Utilizing the native REPLACE function in MSSQL, we can simply pass the {XML Response Payload from “Invoke Web Services”} Published Data into a generic “Query Database” object and then pass the resulting string to the “Query XML” object.

Query Database Object Configuration:

image 

NOTE: This “Query Database” object is not bound to any specific object for the SELECT statement. It simply has a connection to a MSSQL Server and Database.

image

Example Code (from image above):

SELECT REPLACE(REPLACE(' {XML Response Payload from “Invoke Web Services”} ','&lt;','<'),'&gt;','>')

NOTE: Do not copy paste, the above code. The {Published Data} would need to be selected within the object for proper configuration.

Query XML Object Configuration:

image

This way, whatever the NodeData you are looking for, it can be queried as usual using the “Query XML” object with simply XPath statements.

Now, if you wanted to take this a step further, you could do all the parsing (REPLACE and XQuery) within the “Query Database” object, essentially removing the need for the “Query XML” object in this workflow scenario.

Query Database Object Configuration:

image

Example Code (from image above):

DECLARE @x XML SET @x = (SELECT REPLACE(REPLACE(' {XML Response Payload from “Invoke Web Services”} ','&lt;','<'),'&gt;','>'))

SELECT @x.value('(//NodeData1)[1]', 'nvarchar(4)') as [NodeData1], @x.value('(//NodeData2)[1]', 'int') as [NodeData2]

NOTE: Do not copy paste, the above code. The {Published Data} would need to be selected within the object for proper configuration.

enJOY!