Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating XML from select statement Expand / Collapse
Author
Message
Posted Sunday, January 30, 2011 10:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, February 1, 2011 9:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:46 PM
Points: 1,051, Visits: 2,554
Sounds like you should look into FOR XML:

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


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1056929
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse