i am trying to convert a MSSQL query into MySQL

user17328

I need to convert a query that I use in MSSQL into MySQL

 

SELECT company_id,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS [Proposal Number]
FROM
(SELECT company_id,
p_number,CAST('<XMLRoot><RowData>' + REPLACE(replace(p_number,',', '/'), '/','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM TESTERSPLIT)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

dthierjung

As far as I know, MySQL doesn't allow for data to be cast as XML, or at least it isn't one of it's supported cast to data types.

 

However, there are a couple MySQL XML functions you can leverage.

 

Check out this page: https://dev.mysql.com/doc/refman/8.0/en/xml-functions.html

 

They have some pretty good examples which should help convert this query for you.