Convert XML and Parse Results 1

[mwake]

I am using CR 2011 with a SQL Server database as my data source. I'm working for a startup who is developing a mobile app for hailing cabs. In the database there is a fare breakdown field that is in XML. Here is an example of the data:
<FareBreakdown xmlns:xsd="

http://www.w3.org/2001/XMLSchema"

xmlns:xsi="

http://www.w3.org/2001/XMLSchema-in...ip>1.0</Tip><Bonus>20</Bonus></FareBreakdown>

I need a formula which allows me to break down each element into a column, so for each record, I can see the fare paid, the toll, service charge, extras, tip, bonus, etc, in a seperate field. I've never worked with XML before, so I have no idea how to convert it to something I can parse into seperate fields.

 

[IanWaterman]

I had to do something similar and ended up breaking field down using a view/ crystal command

Using Instr() to find location of words like <Fare> and then bring back the adjacent string 56.23 casting to decimal and labelling column as Fare. Repeat for other elements you require.

Then report off that command/view

Ian

 

[IanWaterman]

Sorry getting my Crystal and SQL functions mixed up

patindex('%<Fare>%', yourxmlfield ) will return postion where <fare> starts. You will then also need to find where </fare> starts and using a case statement capture the string between
patindex('%<Fare>%', yourxmlfield )+6 and patindex('%</Fare>%', yourxmlfield ) using a substring function.

If its a big dataset you might want to use a stored procedure to build a table for you to report against.

Ian