Trasform sql query in xml file with child node in the same row-CodePudding

I want to ask you if it's possible with a Sql query when i got a table like this

have an xml output like that:

<Yarn>

<YCode>ACRILINO-1x16</YCode>

<YName>Filato</YName>

<YCount countUnit="Nm">16.00</YCount>

<Ply>1</Ply>

<TwistSense>0</TwistSense>

<Twists lengthUnit="m">222</Twists>

<TwistContraction>0.0</TwistContraction>

<Model>0</Model>

<DimensionFactor>13</DimensionFactor>


-<Fibre>

<Fibre percentage="80">pc</Fibre>

<Fibre percentage="20">li</Fibre>

</Fibre>

</Yarn>

If you see i've the Percentage in the same row, there's a way to do it?

    select ycode,yname,'Nm' as 'YCount/@CountUnit', Ycount,comp1,
case  /*comp1*/
    when  CHARINDEX('%',Comp1)= 0 then LEN(Comp1) 
    else LEFT(Comp1,CHARINDEX('%',Comp1)-1) 
end as 'Fibre/@Percentage',
case  /* Comp2 */
    when  CHARINDEX('%',Comp2)= 0 then LEN(Comp2) 
    else LEFT(Comp2,CHARINDEX('%',Comp2)-1) 
end as 'Fibre/@Percentage',
case
    when CHARINDEX('%',Comp1)= 0 then ''
    else right(Comp1,CHARINDEX('%',Reverse(Comp1))-1) 
end as Fibre,
case
    when CHARINDEX('%',Comp2)= 0 then ''
    else right(Comp2,CHARINDEX('%',Reverse(Comp2))-1) 
end as Fibre
from FILATIXML f
where ycode='AC501'

for xml path ('Yarn')

but i got this error:

Column name 'Fibre/@Percentage' is repeated. The same attribute cannot be generated more than once on the same XML tag.

The problem is how can i've multiple child with the same tag with different fields?

Thank you in advance

CodePudding user response：

You can build the fibre nodes as xml. Demo

with tbl as (
  select 1 id, 'a' c1, 20 p1, 'b' c2, 80 p2 
)    
select id, 
    cast((select p1 [fibre/@percentage], c1 fibre for xml path('')) as xml),
    cast((select p2 [fibre/@percentage], c2 fibre for xml path('')) as xml)
from tbl
for xml path('z');

Returns

<z> 
  <id>1</id>
  <fibre percentage="20">a</fibre>
  <fibre percentage="80">b</fibre>
</z>

CodePudding user response：

Same idea like @Serg just simpler and more performant.

SQL

WITH tbl AS 
(
  SELECT 1 id, 'a' c1, 20 p1, 'b' c2, 80 p2 
)    
SELECT id
    , p1 [fibre/@percentage], c1 AS fibre
    , NULL
    , p2 [fibre/@percentage], c2 AS fibre
FROM tbl
FOR XML PATH('z'), TYPE;

Output

<z>
  <id>1</id>
  <fibre percentage="20">a</fibre>
  <fibre percentage="80">b</fibre>
</z>