[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

SQL Statement

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
Richiep86
Asked:
Richiep86
1 Solution
 
PortletPaulCommented:
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
 
Richiep86Author Commented:
Apologies, its Microsoft SQL.

Thanks for your prompt response,
0
 
Richiep86Author Commented:
MS SQL Server 2005
0
 
Scott PletcherSenior DBACommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now