0

When exporting to xml, I have this output.

"<product><table xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns=""test"">

<row>

...

</row>

<row>

... etc

I need this, instead <row> insert <Product>, without <product ...> and <table> tags on the beginning.

<Product>

...

</Product>

<Product>

... etc

Sql is here.

copy(

select xmlelement(name Product, query_to_xml('

with recursive
cte as(
select
category_id,

...

)
select distinct
s_product.product_id as "itemID",
s_product.product_id as "itemGroupID",

...

string_agg(distinct ''{name:'' || param_name || ''",value:"'' || value || ''"}'','','') as "params",
case when price_tax = price_rec then '''' else ''Akce'' end as "label"

from
s_product
left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join s_category on s_product.category_id = s_category.category_id
left join cte on s_product.category_id = cte.category_id
left join s_cf_j_product_value as a on s_product.product_id = a.product_id
left join s_cf_value as v on a.value_id = v.value_id
left join s_cf_param as p on v.param_id = p.param_id

where
image_order = ''0''
and s_category.category_name is not null
and product_active = ''y''
and s_pricelist_generated_lists.group_id = ''10''
--and s_product.product_id = ''133471''

group by
s_product.product_id,
s_product_image.filename,
s_category.category_id,
s_pricelist_generated_lists.price_tax,
s_pricelist_generated_lists.price_rec,
s_producer.producer_name,
cte.category_name,
cte.category_ids

',false,false,'test'))) to stdout csv

;

Is better xmlforest function for this? I tried, but there was a problem with function string_agg.

Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.