Im pretty new to XML usage, im trying to achieve something like what was asked on this question here Converting rows to XML format in SQL Server But instead of having <tag1>value<\tag1> I need the output to be <tag1=value>, so using the original example the output would be something like <period_sid period_sid=1802 PROJECTION_SALES=1595642.121 PROJECTION_UNITS=18834.75725 ACCOUNT_GROWTH=0 PRODUCT_GROWTH=0>

From what I know, what I need is similar to XML AUTO on SQL servers, but I cant seem to find an equivalent on mYSQL.

Sorry if this is not the propper way of asking, its my very first time using the site :P

CodePudding user response：

Try something like the following (note that the entire attributes a1="" through a5="" are being replaced, not just their values)...

create table Period (
  period_sid int,
  PROJECTION_SALES decimal(18, 3),
  PROJECTION_UNITS decimal(18,5),
  ACCOUNT_GROWTH int,
  PRODUCT_GROWTH int
);

insert Period
  (period_sid, PROJECTION_SALES, PROJECTION_UNITS, ACCOUNT_GROWTH, PRODUCT_GROWTH)
values
  (1802, 1595642.121, 18834.75725, 0, 0);

select
  UpdateXML(UpdateXML(UpdateXML(UpdateXML(UpdateXML(
    '<period_sid a1="" a2="" a3="" a4="" a5="" />',
      'period_sid/@a1', concat('period_sid="', period_sid, '"')),
      'period_sid/@a2', concat('PROJECTION_SALES="', Projection_Sales, '"')),
      'period_sid/@a3', concat('PROJECTION_UNITS="', PROJECTION_UNITS, '"')),
      'period_sid/@a4', concat('ACCOUNT_GROWTH="', ACCOUNT_GROWTH, '"')),
      'period_sid/@a5', concat('PRODUCT_GROWTH="', PRODUCT_GROWTH, '"'))
    as 'Demo'
from Period;

Which yields the output:

<period_sid period_sid="1802" PROJECTION_SALES="1595642.121" PROJECTION_UNITS="18834.75725" ACCOUNT_GROWTH="0" PRODUCT_GROWTH="0" />

CodePudding user response：

It looks like you want attribute-centric results, using FOR XML

You can either use FOR XML PATH with an @ sign before each column:

SELECT
  period_sid AS [@period_sid],
  PROJECTION_SALES AS [@PROJECTION_SALES],
  PROJECTION_UNITS AS [@PROJECTION_UNITS],
  ACCOUNT_GROWTH AS [@ACCOUNT_GROWTH],
  PRODUCT_GROWTH AS [@PRODUCT_GROWTH]
FROM period
FOR XML PATH('period_sid');

Or you can use FOR XML AUTO, however this syntax has less customization possibilities

SELECT
  period_sid,
  PROJECTION_SALES,
  PROJECTION_UNITS,
  ACCOUNT_GROWTH,
  PRODUCT_GROWTH
FROM period AS period_sid
FOR XML AUTO;

db<>fiddle