Creating XML from select statement

• bhushan_sahni

  Grasshopper

  Points: 23

  More actions

  January 30, 2011 at 10:23 pm

  #104035

  I am trying to create an XML from select statement. I have created a Temp table from my query and now I am trying to create the XML. Here is what I have done till now.

  select top 1 1 as Tag,

  null as parent,

  '' as [Invoice!1!Invoice],

  null as [InvoiceDocumentDetails!2!InvoiceDocumentDate],

  null as [InvoiceDocumentDetails!2!InvoiceDocumentNumber],

  null as [InvoiceDocumentDetails!2!DocumentStatus]

  from #tempTable

  union all

  select 2 as tag,

  1 as parent,

  '' as Invoice,

  InvoiceDocumentDate ,

  InvoiceDocumentNumber,

  ''

  from #tempTable

  Now how do I add next tag i.e. TradeAgreementReference and its attributes.

  Here is a part of XML which I need to create

  <Invoice>

  <InvoiceDocumentDetails>

  <InvoiceDocumentDate format="YYYY-MM-DDThh:mm:ss:TZD">2001-12-17T09:30:47</InvoiceDocumentDate>

  <InvoiceDocumentNumber scheme="OTHER">Test invoice</InvoiceDocumentNumber>

  <DocumentStatus codeList="EANCOM">9</DocumentStatus>

  </InvoiceDocumentDetails>

  <TradeAgreementReference>

  <ContractReferenceDate format="YYYY-MM-DDThh:mm:ss:TZD">2001-12-17T09:30:47</ContractReferenceDate>

  <ContractReferenceNumber scheme="OTHER">CG34</ContractReferenceNumber>

  </TradeAgreementReference>

  <OrderReference>

  <PurchaseOrderDate format="YYYY-MM-DDThh:mm:ss:TZD">2001-12-17T09:30:47</PurchaseOrderDate>

  <PurchaseOrderNumber scheme="OTHER">FR453287</PurchaseOrderNumber>

  </OrderReference>

  <OrderConfirmationReference>

  <PurchaseOrderConfirmationDate format="YYYY-MM-DDThh:mm:ss:TZD">2001-12-17T09:30:47</PurchaseOrderConfirmationDate>

  <PurchaseOrderConfirmationNumber scheme="OTHER">785545</PurchaseOrderConfirmationNumber>

  </OrderConfirmationReference>

  <DespatchReference>

  <DespatchDocumentDate format="YYYY-MM-DDThh:mm:ss:TZD">2001-12-17T09:30:47</DespatchDocumentDate>

  <DespatchDocumentNumber scheme="OTHER">687334</DespatchDocumentNumber>

  </DespatchReference>

  <ReceiptAdviceReference>

  <ReceiptAdviceDocumentDate format="YYYY-MM-DDThh:mm:ss:TZD">2001-12-17T09:30:47</ReceiptAdviceDocumentDate>

  <ReceiptAdviceDocumentNumber scheme="OTHER">GUY656</ReceiptAdviceDocumentNumber>

  </ReceiptAdviceReference>

  <Buyer>

  <BuyerGLN scheme="GLN">5013546085276</BuyerGLN>

  <BuyerAssigned scheme="OTHER">Compass</BuyerAssigned>

  <SellerAssigned scheme="OTHER">A Supplier</SellerAssigned>

  <Address>

  <BuildingIdentifier scheme="OTHER">10</BuildingIdentifier>

  <StreetName scheme="OTHER">Maltravers Street</StreetName>

  <City scheme="OTHER">London</City>

  <PostCode scheme="OTHER">WC2R 3BX</PostCode>

  <Country codeList="ISO">GB</Country>

  </Address>

  </Buyer>

  <\Invoice>

  Please help me out. I hope I am clear, if not please let me know. Thanks in advance

• Jason Wolfkill

  SSCrazy Eights

  Points: 9832

  More actions

  February 1, 2011 at 9:46 am

  #1281027

  Sounds like you should look into FOR XML:

  http://msdn.microsoft.com/en-us/library/ms191268.aspx

  Jason Wolfkill