topic Example attached, you'll… in Product Forum

How to convert xml data to collection

AmitPatnaik — Thu, 01 Mar 2018 15:55:00 GMT

I want my entire xml data to be converted to collection. Thanks in advance.

A very vague statement but…

John__Carter — Thu, 01 Mar 2018 17:02:00 GMT

A very vague statement but you're likely to need to create a bespoke code stage to parse the XML input into a .Net Datatable output.

Sorry for the unclear…

AmitPatnaik — Fri, 02 Mar 2018 10:49:00 GMT

Sorry for the unclear statement : Here is the requirement--- Matthew XML Developer's Guide Computer 44.95 2000-10-01 An in-depth look at creating applications with XML. Ralls, Kim Midnight Rain Fantasy 5.95 2000-12-16 A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world. --------------------------------- output : author | title | genre | price | publish_date | description and the 2 corresponding values Hope I gave proper explanation. Is this can be done by existing VBO or the code stage is the only solution

You probably would need your…

John__Carter — Fri, 02 Mar 2018 14:28:00 GMT

You probably would need your own code stage - the nearest example is in the Utility String object, on the pages Get XML Element and Get XML Attribute. You also use standard text manipulation for this example - use the Split Text action of the Utility String object to make a collection with each row containing a book element text. Then loop through the collection, extract what you want from each row and add it to a second collection that has the author | title | genre | price | publish_date | description fields.

Example attached, you'll…

DavidEdwards-Da — Fri, 02 Mar 2018 16:01:00 GMT

Example attached, you'll need to rename it to .bprelease (the forums don't allow that file extension to be attached)

Thanks David for the help…

AmitPatnaik — Mon, 05 Mar 2018 11:28:00 GMT

Thanks David for the help It worked great

Thank you for sharing, David…

AlexanderBopp — Mon, 23 Jul 2018 20:58:00 GMT

Thank you for sharing, David. Great, simple and very helpful!

RE: Example attached, you'll…

JUNCHOU — Thu, 19 Nov 2020 02:37:00 GMT

Hi David,
Can you share with me the bprelease for this function. Thanks in advance .

------------------------------
JUN CHOU
Analyst
IBM BPSO
Asia/Shanghai
------------------------------

RE: Example attached, you'll…

İpekIşıldak — Fri, 15 Apr 2022 07:56:00 GMT

Hi David,

Can you please share with me the bp release?
I coulnd't see the attachment..

------------------------------
İpek Işıldak
RPA Developer
Aksigorta
------------------------------

RE: Example attached, you'll…

ewilson — Sun, 17 Apr 2022 15:38:00 GMT

Hello İpek,

David's original post was from 2018. My guess is his attachment may have been lost when we converted from one community platform to the current platform. Having said that, you can implement XML to Collection conversion fairly easily with a Code stage, but unless you XML is very simple I don't think the output is going to be what you're expecting.

The issue here is that most XML tends to be fairy complex in terms of elements within elements and attributes on elements. This doesn't actually map very well to a Collection unless you define the schema yourself upfront. If you leave it to .NET to infer the schema of the data what typically happens is you end up with multiple DataTables (i.e. BP Collections).

Within .NET there's the System.Data namespace which contains the DataSet and DataTable class definitions. Contrary to most examples you find on the internet, you will not be able to convert straight from XML to a DataTable - unless your XML is VERY simple (i.e. only single depth). Instead, you will need to make use of the DataSet class and then pull your specific DataTable out of the DataSet using the Tables attribute. Here's a brief example function in VB.Net:

Public Function ConvertXMLToDataTable(ByVal xmlData As String) As DataTable
    Dim stream As StringReader = Nothing
    Dim reader As XmlTextReader = Nothing

    Try
        Dim xmlDS As DataSet = New DataSet()
        stream = New StringReader(xmlData)
        reader = New XmlTextReader(stream)
        xmlDS.ReadXml(reader)
        Return xmlDS.Tables(0)
    Catch
        Return Nothing
    Finally
        If reader IsNot Nothing Then reader.Close()
    End Try
End Function

NOTE: This is just one example of how to do it. There are various differences in coding this.

As I mentioned above, I don't think this will ultimately give you what you're expecting, but give it a try and see what you think. Alternatively, you can use the Utility - XML VBO, from the Digital Exchange, the help you process any XML you have.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------