Skip to content

    • Datasets

    Datasets

    i am trying to convert a MSSQL query into MySQL

    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

    • 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.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories