i am trying to convert a MSSQL query into MySQL

Options
user17328
user17328 Member
edited March 2023 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]
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)

Comments

  • dthierjung
    dthierjung Contributor
    Options

    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.

Categories