Issue manipulating data

[niall5098]

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

 

[Olaf Doschke]

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

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.