2 Replies - 979 Views - Last Post: 26 June 2013 - 12:44 PM

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

sql query and crystal not returning info how i want it

Posted 24 June 2013 - 09:18 AM

Let's say i have xml that looks like the following:


  <Item Field="Condition:Conditions" Group="Quality Failure">some stuff </Item>
  <Item Field="Condition" Group="Quality Failure">some stuff </Item>
  <Item Field="Condition" Group="Quality Failure">some stuff </Item>

  <Item Field="90-120 Day Suggested List Price:List Price" Group="Quality Failure">some stuff </Item>
  <Item Field="90-120 Day Probable Sales Price:Comp Sale Value Low" Group="Quality Failure">some stuff </Item>
  
</QA>







my crystal returns the following

Quote

"Sale #1 Closed Date should be within 6 months of Date of Inspection. Please provide detailed commentary in the Sold Comparable #1 Commentary section if you must exceed this parameter. Please include details like the number of similar sales within the past 6 months and why they were not accurate comparables, demographics and statistics on this market, and any other information to explain exceeding the 6-month parameter
Sale #3 Closed Date should be within 6 months of Date of Inspection. Please provide detailed commentary in the Sold Comparable #3 Commentary section if you must exceed this parameter. Please include details like the number of similar sales within the past 6 months and why they were not accurate comparables, demographics and statistics on this market, and any other information to explain exceeding the 6-month parameter"


my query

replace(dbo.get_xml_values(t_Order_Detail_Task_Data.xml_data,'QA/Item'),'|',char(13)) as QA_Description,







my question is how can i return each item on its own individual line because as you can see from above there are mulitple items with the same fields and groups but the client wants each item on a seperate line.



Thanks

This post has been edited by DarenR: 24 June 2013 - 09:18 AM


Is This A Good Question/Topic? 0
  • +

Replies To: sql query and crystal not returning info how i want it

#2 P4L   User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,789
  • Joined: 07-February 08

Re: sql query and crystal not returning info how i want it

Posted 26 June 2013 - 11:23 AM

If you're using Crystal, you can do this simply by checking the "Underlay Following Section" in the Section Expert. This should put everything on one line. If that doesn't work, and you don't have multiple details for each group, you can just move your detail fields into the group section you want it in.
Was This Post Helpful? 0
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 637
  • View blog
  • Posts: 4,226
  • Joined: 12-January 10

Re: sql query and crystal not returning info how i want it

Posted 26 June 2013 - 12:44 PM

Ufortunately -- it's not the one line issue. I needed it to be multiple lines. What we ended up having to do was create a function that returns a table of tables and pulled the information out that way. Took a lot of internet research to figure out how to pull identical xml nodes into unique nodes.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1