Crystal Reports using XML data set

[prgmrcr]

Hello Members,
I have to develop a report and show the data that is stored in xml in a column in the physical table.It comes down to creating a dataset out of a query. In Crystal reports, i have developed a report using xml file as dataset and it works great. How can i develop a report if that information is stored in a column in physical table. Any imputs are greatly appreciated.


Thanks in advance!!!

 

[prgmrcr]

Pulling the data is not a problem. I have to display the data and format based on th xml code. The data is stored in xml format in the column. I have tired html text interpretation but that did not work.

For example this is a sample xml code:

<?xml version="1.0" standalone="yes" ?>
- <DataSet1 xmlns="

http://tempuri.org/DataSet1.xsd">

- <Product>
<product_id>1</product_id>
<product_name>Product1</product_name>
<product_price>10</product_price>
</Product>
- <Product>
<product_id>2</product_id>
<product_name>Product2</product_name>
<product_price>15</product_price>
</Product>
- <Product>
<product_id>3</product_id>
<product_name>Product3</product_name>
<product_price>350</product_price>
</Product>
- <Product>
<product_id>4</product_id>
<product_name>Product4</product_name>
<product_price>75</product_price>
</Product>
- <Product>
<product_id>5</product_id>
<product_name>Product5</product_name>
<product_price>100</product_price>
</Product>
- <Product>
<product_id>6</product_id>
<product_name>Product6</product_name>
<product_price>600</product_price>
</Product>
</DataSet1>


This how the data is stored in the column. Out of this you have to read the data.

data should be displayed in the report as :


product_id product_name product_price
1 Product1 10
2 Product2 15
3 Product3 350
4 Product4 75
5 Product5 100
6 Product6 600

Thanks.

 

[GJParker]

Unfortunately as far as CR is concerned it will see this as a string data type so you would need parse each bit of the information from the string using a formula.

What db are you using and what is the data type of the field on the db?



Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

In your example above, are the lines divided by line returns, e.g., chr(13) or chr(10)? You could use a variable to extract the desired value from each line by using:

extractstring({table.field},">","<")

...but you would need to loop through the "lines" by segmenting them based on the return code.

-LB