query and compare values in a text based column

Question

query and compare values in a text based column

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 1

drew.allen (10/7/2016)
niall5098 (10/7/2016)
HI There,
it looks like sql but is actually text
We said it was XML, not SQL, and XML IS text, but in a very specific format. Even if it's not XML, it contains XML and you can trick SQL into treating it as XML, which is going to be a lot easier than any other method. We would still need a sample of the data.
Drew

duh. Stupid me. I forget that stuff sometimes. Thanks for the correction.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

drew.allen SSC Guru Points: 76945 More actions · Answer 2

Try the following.

DECLARE @t TABLE(doc XML)

INSERT @t (doc)

VALUES('

<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>')

SELECT f.fields.value('old_pmetd_refno[1]/text()[1]', 'INT'), f.fields.value('new_pmetd_refno[1]/text()[1]', 'INT')

FROM @t

CROSS APPLY doc.nodes('/AUDITS/Fields') f(fields)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

niall5098 Say Hey Kid Points: 689 More actions · Answer 3

HI Drew,

that's the output I want. thanks for that code. the thing is getting to that point.

i need to populate the table (@t TABLE(doc XML) with all new rows from the original table\column for the last 24 hours and then query that data to get the above output.

is it possible to declare the temp table, and populate it using an insert into statement?

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

niall5098 (10/7/2016)
Hi There,
please see example of data below:
<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>

Jeez... as a bit of a sidebar, someone needs to find the "developer" that created all that and put them out of their misery. What a huge and ridiculous waste of space and resource usage for something that should have been flat data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

niall5098 Say Hey Kid Points: 689 More actions · Answer 5

niall5098

Say Hey Kid

Points: 689

October 13, 2016 at 8:45 am

#1906350

AGREED

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 6

niall5098 (10/13/2016)
HI Drew,
that's the output I want. thanks for that code. the thing is getting to that point.
i need to populate the table (@t TABLE(doc XML) with all new rows from the original table\column for the last 24 hours and then query that data to get the above output.
is it possible to declare the temp table, and populate it using an insert into statement?

Only if the table has a column that identifies the date and time of when the audit row was created.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

niall5098 Say Hey Kid Points: 689 More actions · Answer 7

HI Jeff,

the original table does have a modified column so the idea is I would querying for data which has a modified date > getdate()-1

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 8

niall5098 (10/13/2016)
HI Jeff,
the original table does have a modified column so the idea is I would querying for data which has a modified date > getdate()-1

I'm certainly not an XML expert but I believe that all you need to do is add the appropriate WHERE clause and, perhaps, the date/time column to the select list for reference, to Drew's good code and you have the full result set you want. You can add an INTO #table name to direct the result set to a new Temp Table or pre-create it and use INSERT/SELECT to populate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Celko SSCertifiable Points: 5749 More actions · Answer 9

Jeez... as a bit of a sidebar, someone needs to find the "developer" that created all that and put them out of their misery. What a huge and ridiculous waste of space and resource usage for something that should have been flat data.

Amen! In a well-designed system, we do not mix languages together. Each one has a purpose, a domain and is to for that purpose and domain. Mixing them together like this screws up everybody.

There used to be a gag greeting card many years ago that began with a cartoon Chinese guy on the front page, and the caption "remember the old Chinese saying" and you opened the card, you are greeted with a whole page of bizarre Chinese characters. I have the feeling those characters were like the exhibit currently at the Blanton Museum here in Austin (Xu Bing: Book from the Sky).

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

niall5098 Say Hey Kid Points: 689 More actions · Answer 10

HI All,

thanks for the help. I got a solution which seems to work so I'll put it here in case someone else has the issue:

SELECT top 10 full_record,

CASE WHEN CHARINDEX('<new_PMETD_REFNO>', full_record) > 0 THEN

CASE WHEN SUBSTRING(full_record, CHARINDEX('<new_PMETD_REFNO>', full_record) + LEN('<new_PMETD_REFNO>'), 4) <> SUBSTRING(full_record, CHARINDEX('<old_PMETD_REFNO>', full_record) + LEN('<old_PMETD_REFNO>'), 4) THEN 'Diff'

ELSE 'Same' END

ELSE 'No Payment methods Changed' END as Result,

SUBSTRING(full_record, CHARINDEX('<new_PMETD_REFNO>', full_record) + LEN('<new_PMETD_REFNO>'), 4) as newpayment,

SUBSTRING(full_record, CHARINDEX('<old_PMETD_REFNO>', full_record) + LEN('<old_PMETD_REFNO>'), 4) as oldpayment

FROM [dbo].[EXTRACTS]

WHERE CHARINDEX('<new_PMETD_REFNO>', full_record) > 0 and MODIF_DTTM > GETDATE() -7

AND SUBSTRING(full_record, CHARINDEX('<new_PMETD_REFNO>', full_record) + LEN('<new_PMETD_REFNO>'), 4) <> '</ne' --When blank

AND SUBSTRING(full_record, CHARINDEX('<old_PMETD_REFNO>', full_record) + LEN('<old_PMETD_REFNO>'), 4) <> '</ol' --When blank

AND SUBSTRING(full_record, CHARINDEX('<new_PMETD_REFNO>', full_record) + LEN('<new_PMETD_REFNO>'), 4) <> SUBSTRING(full_record, CHARINDEX('<old_PMETD_REFNO>', full_record) + LEN('<old_PMETD_REFNO>'), 4)

drew.allen SSC Guru Points: 76945 More actions · Answer 11

Why did you abandon the XML approach? It performs better than manipulating the strings.

I did notice that the sample XML data you provided does not match the case of the fields in your "solution". XML is case-sensitive, so the cases need to match.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA