Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crystal Reports using XML data set

Status
Not open for further replies.

prgmrcr

Programmer
May 11, 2009
56
US
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!!!
 
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="- <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.
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top