Solved

SQL Statement

Posted on 2014-09-30
6
124 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
Comment Utility
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
Comment Utility
Apologies, its Microsoft SQL.

Thanks for your prompt response,
0
 

Author Comment

by:Richiep86
Comment Utility
MS SQL Server 2005
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

10 Experts available now in Live!

Get 1:1 Help Now