INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Issue manipulating data

Issue manipulating data

(OP)
HI There,

as the heading suggests I am trying to query a text based audit column for specific values then compare them values. For example the column should contain values for new_ref_no and old_ref_no in the straing. I want to query for those and then compare them to see if they are in fact different. However I am not sure how I would go about doing this.

the column is TEXT datatype but the string in the column is stored like xml, for example:

<AUDITS>
<Admin>
</Admin>
<provider_spells>
<Refno>123456</Refno>
<Action>UPDATE</Action>
<Fields>
<old_pmetd_refno></old_pmetd_refno>
<new_pmetd_refno>4110</new_pmetd_refno>
</Fields>
</AUDITS>

So what I think (or would like to do ) would work is if I can somehow extract the data in this column alone and insert into a temp table as XML is hould be able to query and compare. However I am not sure who to insert into a temp table and convert the data to xml.

any ideas are most welcome.

Thanks

RE: Issue manipulating data

First of all a working sample of what you may do:

CODE -->

declare @test as table (txml Text);
insert into @test values ('<AUDITS>
<Admin>
</Admin>
<provider_spells />
<Refno>123456</Refno>
<Action>UPDATE</Action>
<Fields>
<old_pmetd_refno>0000</old_pmetd_refno>
<new_pmetd_refno>4110</new_pmetd_refno>
</Fields>
</AUDITS>');


declare @xmltest as table (xxml XML);
Insert into @xmltest Select Cast(txml as XML) from @test

SELECT x.xxml.value('(//old_pmetd_refno)[1]','int') as oldrefno,
x.xxml.value('(//new_pmetd_refno)[1]','int') as newrefno
from @xmltest x 

First: to use T-SQL xml functionalities, you need to store xml in XML type columns. I did define a Text column and CASTed it to XML. I needed to mend your XML for this to work, your <provider_spells> had no closing tag, so I made it <provider_spells />, that might simply be an issue with the small snippet you copied over, maybe not a problem in your data.

For what it's worth, I'd not define a Text column at all, but work with XML to start with.

Next step is already the final step for getting the values as oldrefno and newrefno columns: XML.value(XPathQuery, SQLServerType) is the xml function I used to extract the values from the XML, assuming always one row only in your xml, this picks out each first value of the node. If each XML snippet has multiple rows you need to use nodes and convert the xml nodes of interest into a table.

You final milage now is to query where oldrefno=newrefno to see updates which did not change the refno, for example.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close