i have more 3 tables. simplicity, let take 3 products, productbrands , productattributes. every poduct have 0 or more brands , 0 or more attributes. have,
select p.name,p.id, p.desc products but want select product attributes , brands in same sql. thinking this,
select p.name,p.id, p.desc, getproductattributesinjsonorxml(p.id), getproductbrandsinjsonorxml(p.id) products how create getproductattributesinjsonorxml , getproductbrandsinjsonorxml funstions? in app can deserilize xml or json. please let me know if there better way.
you can select data in sql server xml use of for xml clause. such query give single row single column containing generated xml. here's example.
you use this:
select product.name, product.id, product.desc, attribs.attribute, brands.brand products product left join productbrands brands on product.id = brands.productid left join productattributes attribs on product.id = attribs.productid xml auto, elements to xml schema this, 1 product group each row:
<product> <name></name> <id></id> <desc></desc> <attribs> <attribute></attribute> </attribs> <brands> <brand></brand> </brands> </product> ... there lot of different options clause schema formatted way want, though might take bit of work more complicated designs.
there's no way generate json on sql server, short of using code explicitly generate text functions. complicated , not perform well, since sql server not optimized text processing. generating json best done @ application level.
if need emit both json , xml, suggest generating both @ application level. allows needing 1 sql query data, , keeping data formatting code in single place.
Comments
Post a Comment