i am trying to convert a MSSQL query into MySQL

edited March 13 in Datasets

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]
(SELECT company_id,
p_number,CAST('<XMLRoot><RowData>' + REPLACE(replace(p_number,',', '/'), '/','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)


  • dthierjung
    dthierjung Contributor

    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.