CONVERT SQL STATEMENT INTO FETCH - BASED XML

• Tuesday, May 15, 2012 6:17 PM

   

   
  

  0

  I'm a newbie with fetch based XML. Can somebody give me some hints what I should do to get the SQL Statement below converted to fetch - based XML. Help will be much appreciated. :-)

  SELECT TOP (10000) dbo.FilteredAccount.territoryidname AS Territory, dbo.FilteredOpportunity.owneridname AS Owner, dbo.FilteredOpportunity.name AS Topic, 
  dbo.FilteredOpportunity.customeridname AS [Potential Customer], dbo.FilteredOpportunity.new_contactpersonidname AS Contactperson, 
  dbo.FilteredOpportunity.statuscodename AS StatusFase, dbo.FilteredOpportunity.statecodename AS Status, dbo.FilteredOpportunity.createdbyname AS [Created By], 
  dbo.FilteredOpportunity.createdon AS [Created On], dbo.FilteredOpportunity.modifiedbyname AS [Modified By], dbo.FilteredOpportunity.modifiedon AS [Modified On], 
  dbo.FilteredOpportunity.estimatedclosedate AS [Est. Close Date], dbo.FilteredOpportunity.actualclosedate AS [Actual Close Date], 
  dbo.FilteredOpportunityProduct.opportunityid AS LinjeId, dbo.FilteredOpportunity.opportunityid, 
  dbo.FilteredOpportunityProduct.transactioncurrencyidname AS Currency, dbo.FilteredOpportunity.isrevenuesystemcalculatedname AS Revenue, 
  dbo.FilteredOpportunityProduct.productid AS ProductId, dbo.FilteredOpportunityProduct.productidname AS [Product Name], 
  dbo.FilteredOpportunityProduct.quantity AS Quantity, dbo.FilteredOpportunityProduct.exchangerate AS Exchangerate, 
  dbo.FilteredOpportunityProduct.baseamount AS Baseamount, dbo.FilteredOpportunityProduct.baseamount_base AS Baseamount_Base, 
  dbo.FilteredOpportunityProduct.extendedamount AS ExtendedAmount, dbo.FilteredOpportunityProduct.extendedamount_base AS ExtendedAmount_Base, 
  dbo.FilteredOpportunityProduct.new_costprice AS New_Costprice, dbo.FilteredOpportunityProduct.new_costprice_base AS New_Costprice_Base, 
  dbo.FilteredOpportunityProduct.new_discount AS New_Discount, dbo.FilteredOpportunityProduct.new_discount_base AS New_Discount_Base, 
  dbo.FilteredOpportunityProduct.new_listprice AS New_Listprice, dbo.FilteredOpportunityProduct.new_listprice_base AS New_Listprice_Base, 
  dbo.FilteredOpportunityProduct.new_priceoverridden AS Ndew_Priceoverridden, 
  dbo.FilteredOpportunityProduct.new_priceoverriddenname AS New_Priceoverridden_Base, dbo.FilteredOpportunityProduct.new_priceperunit AS Nrew_Priceperunit, 
  dbo.FilteredOpportunityProduct.new_priceperunit_base AS New_Priceperunit_Base, dbo.FilteredOpportunityProduct.priceperunit AS Priceperunit, 
  dbo.FilteredOpportunityProduct.priceperunit_base AS Priceperunit_Base, dbo.FilteredOpportunityProduct.tax AS Tax, 
  dbo.FilteredOpportunityProduct.tax_base AS Tax_Base, dbo.FilteredOpportunityProduct.tax AS Expr1, dbo.FilteredOpportunityProduct.tax_base AS Expr2, 
  dbo.FilteredOpportunityProduct.productdescription, dbo.FilteredOpportunityProduct.owninguser, dbo.FilteredOpportunityProduct.owningbusinessunit, 
  dbo.FilteredOpportunityProduct.pricingerrorcodename AS [Pricing Error Code Name], dbo.FilteredOpportunityProduct.uomidname AS Unit, 
  dbo.FilteredProduct.producttypecodename
  FROM dbo.FilteredAccount INNER JOIN
  dbo.FilteredOpportunity ON dbo.FilteredAccount.accountid = dbo.FilteredOpportunity.accountid AND 
  dbo.FilteredAccount.accountid = dbo.FilteredOpportunity.accountid INNER JOIN
  dbo.FilteredOpportunityProduct ON dbo.FilteredOpportunity.opportunityid = dbo.FilteredOpportunityProduct.opportunityid INNER JOIN
  dbo.FilteredProduct ON dbo.FilteredOpportunityProduct.productid = dbo.FilteredProduct.productid

  ---

  Hans Inge

  • Reply
  • Quote