Creating XML from select statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » XML » Creating XML from select statement

Creating XML from select statement

Rate Topic

Display Mode

Topic Options

Author

Message

bhushan_sahni

Posted Sunday, January 30, 2011 10:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 12:49 AM
Points: 1, Visits: 13

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

Post #1055971

wolfkillj

Posted Tuesday, February 01, 2011 9:46 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 6:54 PM
Points: 721, Visits: 1,375

Sounds like you should look into FOR XML:

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

Post #1056929

« Prev Topic | Next Topic »

Permissions