Solved

SQL Statement

Posted on 2014-09-30
6
126 Views
Last Modified: 2014-12-15
Hi All,

I  have a large database with a collumn called RegDataXml which has around 50 lines of XML within it.

Within this large XML are 2 tags <frn> 218 805 </frn> which i need the query to return.

HOWEVER, Following this, i would ONLY like to return the FRN numbers which ARE ABOVE 100 000, Can any one assist me?

Many thanks,

Richard
0
Comment
Question by:Richiep86
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40351748
well you have not helped by choosing 3 different databases...

the syntax for retrieving XML is different in Oracle to MS SQL Server (and I presume MySQL will also be different)

Please, which database is it? (and what version, it may make a difference)
0
 

Author Comment

by:Richiep86
ID: 40351829
Apologies, its Microsoft SQL.

Thanks for your prompt response,
0
 

Author Comment

by:Richiep86
ID: 40351832
MS SQL Server 2005
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40352484
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40355439
If "<frn>" will appear only once (or none) in the column, you could also convert the xml to varchar and use CHARINDEX().  For example:


SELECT frn_value, ...
FROM table_name
CROSS APPLY (
    SELECT CAST(RegDataXml AS varchar(max)) AS RegDataVarchar
) AS assign_aliases_1
CROSS APPLY (
    SELECT CHARINDEX('<frn>', RegDataVarchar) AS RegData_start_of_frn
) AS assign_aliases_2
CROSS APPLY (
    SELECT LTRIM(RTRIM(CASE WHEN RegData_start_of_frn = 0
        THEN ''
        ELSE SUBSTRING(RegDataVarchar, RegData_start_of_frn + 5, CHARINDEX('</frn>', RegDataVarchar) - RegData_start_of_frn)
        END)) AS frn_value
) AS assign_aliases_3
WHERE
    frn_value >= '100 000'
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now