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.

LVL 9
apirniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.