• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Cursor to do line by line data manipulation

I have a table with the following format:

select sField, sCell, sValue from AnswerBank where fkUserOrder = 1697004

sField                       sCell                                          sValue
--------                     --------                                       --------
business_zip      NA                                      19701
Business_Phone      NA                                  302-222-2222
Business_City      NA                            Bear
Corp_owner_info      Corp_owner_info_State_1      CA
Corp_owner_info      Corp_owner_info_Full_Name_1      Lance Burris

So I run a query for an order number and I get above list. Now I want to concatenate XML tags to my answers depending on the value of the sField and sCell.

For example is sFiels = 'business_zip' and sCell= NA then I should have:
<zip>19701</zip>
However if sCell is not = NA I have to use the value from the sCell for my tag comparison

So:
If sCell = 'Corp_owner_info_State_1' then my tag is:
<state>CA</state>

I need help on doing this... I will open more questions to add more points if this is to complicated.

0
apirnia
Asked:
apirnia
  • 5
  • 4
1 Solution
 
Scott PletcherSenior DBACommented:
How do you know that the tag will be "zip" or "state"?  Is this done by parsing the actual values of the columns or are *you* applying some other logic of your own?
0
 
apirniaAuthor Commented:
I will hardoce that part in.

so for  'business_zip' the tag is always  <zip>
and for 'Corp_owner_info_State_1' is always <state>

and these wont chnage. They are standard for all orders. So If I do bunch of case or if statements it will always stay the same.
0
 
apirniaAuthor Commented:
Then at the end I want to save the results in a Text file. If thats not possible I can insert into a temp table and then at the end read them to a text file
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
Best would be to create a table to store the conversions.  For example:

lookupTable
    code   matches value in SCell / SField
    tag      contains the tag to be used for that code


Then:

select '<' + COALESCE(lk1.tag, lk2.tag) + '>' +
    value +
    '</' + COALESCE(lk1.tag, lk2.tag) + '>'
from AnswerBank ab
left outer join lookupTable lk1 on lk1.code = ab.SCell
left outer join lookupTable lk2 on lk2.code = ab.SField
where fkUserOrder = 1697004
0
 
Scott PletcherSenior DBACommented:
Oh, forgot about the file output.  Probably easiest is to load the results into a temp table and then dump the table to a file using bcp.
0
 
apirniaAuthor Commented:
The look up table concept sounds interesting, but I am not sure how I would take care of my tested tags.

for some of the fields I have nested format and some don't. How would I take care of that part?


e.g
<entry>
      <string>CONCURRENT</string>
      <statement id="14">
      <key />
      <args id="15">
      <string />
      </args>
      </statement>
</entry>
0
 
Scott PletcherSenior DBACommented:
Not sure; don't know enough of the details of what you are trying to do to assess that.
0
 
apirniaAuthor Commented:
What do you think about this soloution to do the nesting:

I added another field called 'sGrouping' to the lookup table. This tells me if the value is nested under another tag what is that tag name. Then I do the following query in an ASP page and do the nesting in my ASP.

select distinct '<' + COALESCE(lk1.sTag, lk2.sTag) + '>' +
    sValue +
    '</' + COALESCE(lk1.sTag, lk2.sTag) + '>' , lk2.sGrouping
from AnswerBank ab
left outer join MarkLookUp lk1 on lk1.sCode = ab.SCell
left outer join MarkLookUp lk2 on lk2.sCode = ab.SField
where fkUserOrder = 1697004

Any suggestions?
0
 
Scott PletcherSenior DBACommented:
Not really.  I don't fully understand how you know that something needs to be nested and the tag(s) to use for the nesting.  Perhaps you can just keep adjusting the statement and testing until you get the results you need.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now