Generate XML from Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generate XML from Query

Author

Topic

jamie1984
Starting Member

6 Posts

Posted - 2008-11-06 : 06:45:31

Sorry this is a long post...can any one help?

I have developed a Purchase Order System in asp.net and require an export of purchase orders in XML format.

I have managed to achieve this but not in the way I want.

To simplify

tblPurchaseOrder
----one to many----
tblPurchaseOrderLines

A purchase order can have many po lines.

I have designed an xml structure


<?xml version="1.0" encoding="utf-8" ?> 
<POSystem>
 <ePurchaseOrder>
   <ePONumber>00005</ePONumber> 
   <ePOType>Fixed Asset</ePOType> 
   <ResourceID>abc</ResourceID> 
   <ApproverID>def</ApproverID> 
   <VendorID>ghi</VendorID> 
   <DateSubmitted>01-07-2008</DateSubmitted> 
   <CurrencyCode>Europe</CurrencyCode> 
   <TotalPOValue>32.0</TotalPOValue> 
     <ePurchaseOrderLine> 
      <Description>Test</Description> 
      <Rechargeable>No</Rechargeable> 
      <Quantity>2</Quantity> 
      <UnitPrice>3.50</UnitPrice> 
      <LineTotal>7.00</LineTotal> 
     </ePurchaseOrderLine>
     <ePurchaseOrderLine>
      <Description>my test</Description> 
      <Rechargeable>No</Rechargeable> 
      <Quantity>5</Quantity> 
      <UnitPrice>5.00</UnitPrice> 
      <LineTotal>25.00</LineTotal> 
     </ePurchaseOrderLine>
 </ePurchaseOrder>
</POSystem>

I am unable to put together the stored proc to generate the multiple PO lines in XML.

What I have done is put together a view containing the fields I want to export from tblPurchaseOrder and another view for the fields from tblPurchaseOrderLines.

My SP can return the data from the tblPurchaseOrder but not sure how to modifiy it to incorporate the lines.


USE [epo]
GO
/****** Object:  StoredProcedure [dbo].[sp_PurchaseOrder_XML__]    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_PurchaseOrder_XML__] 
(
@poid int
)

AS 
BEGIN

	SET NOCOUNT ON;

--    DECLARE @del_count int
--    DECLARE @ins_count int
    DECLARE @XMLPath VARCHAR(4048)
    --SELECT @del_count = count(*) FROM deleted
    --SELECT @ins_count = count(*) FROM inserted
    SELECT @XMLPath = [path] FROM [tblXMLSetup]

	--******check for xml export path*****
    IF @XMLPath = '' 
    RAISERROR('path is not setup in tblXMLSetup',15,1)
	--******end xml path check*****
	
	--******declare fields for export*****
    DECLARE @PurchaseOrderID int
	DECLARE @ePOType varchar(20)
	DECLARE @EmpNoRef varchar(10)
        DECLARE @ApproverNoRef	varchar(10)
	DECLARE @DateSubmitted datetime
	DECLARE @CurrencyCode varchar(10)
	DECLARE @VendorIDRef varchar(10)
	DECLARE @TotalValue decimal(9,2)

	--******declare xml prefix tag and end tag******
    declare @xmlpref varchar(100)
    declare @xmlfin varchar(4048)
    declare @xml varchar(8000)
	declare @xml2 varchar(8000)
    DECLARE @ROWS int
    
    BEGIN 
    

        Set @xmlpref = ('<?xml version="1.0" encoding="UTF-8" standalone="no" ?>') 
 
        Set @xml = 
		(
          
		  SELECT 

			[PONumber] AS ePONumber, 
			[POOrderType] AS ePOType,
			[POEmployeeNumber] AS ResourceID,
			[POApproverNumber] AS ApproverID,
			[VendorID] AS VendorID,
			[PODateRaised] AS DateSubmitted, 	
			[POCurrencyCode] AS CurrencyCode,	
			[POTotalValue] AS TotalPOValue
          
		  FROM viewPO  
	    
		  WHERE ([POStatus]='0' 

		  AND [POWizardComplete]='1' 
		  AND [PONumber] = @poid)

		  FOR XML RAW ('ePurchaseOrder'),ELEMENTS


		)   


		  UPDATE [tblPurchaseOrder] 
SET [DateSent]=getdate(), [Status]='4' WHERE PurchaseOrderID = @poid

		IF @xml <> '' BEGIN
          set @xmlfin = (@xmlpref+@xml)

          SET @XMLPath = @XMLPAth +'\ePO_'+
            CONVERT(varchar,DATEPART(yyyy,GETDATE()))+
            CONVERT(varchar,DATEPART(mm,GETDATE()))+
            CONVERT(varchar, DATEPART(dd,GETDATE()))+
            '_'+
            CONVERT(varchar, DATEPART(hh,GETDATE()))+
            CONVERT(varchar, DATEPART(mi,GETDATE()))+
			CONVERT(varchar, DATEPART(ms,GETDATE()))+
            '.XML' 
            exec dbo.writetofile @xmlfin, @XMLPath
       END
    END
  END

Apologies if anything is unclear.

Many Thanks,
J.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 08:32:36

[code]DECLARE @xml XML

SET @xml = '<?xml version="1.0" encoding="utf-8" ?>
<POSystem>
<ePurchaseOrder>
<ePONumber>00005</ePONumber>
<ePOType>Fixed Asset</ePOType>
<ResourceID>abc</ResourceID>
<ApproverID>def</ApproverID>
<VendorID>ghi</VendorID>
<DateSubmitted>01-07-2008</DateSubmitted>
<CurrencyCode>Europe</CurrencyCode>
<TotalPOValue>32.0</TotalPOValue>
<ePurchaseOrderLine>
<Description>Test</Description>
<Rechargeable>No</Rechargeable>
<Quantity>2</Quantity>
<UnitPrice>3.50</UnitPrice>
<LineTotal>7.00</LineTotal>
</ePurchaseOrderLine>
<ePurchaseOrderLine>
<Description>my test</Description>
<Rechargeable>No</Rechargeable>
<Quantity>5</Quantity>
<UnitPrice>5.00</UnitPrice>
<LineTotal>25.00</LineTotal>
</ePurchaseOrderLine>
</ePurchaseOrder>
</POSystem>'

SELECT s.po.value('ePONumber[1]', 'VARCHAR(20)') AS ePONumber,
s.po.value('ePOType[1]', 'VARCHAR(20)') AS ePOType,
s.po.value('ResourceID[1]', 'VARCHAR(20)') AS ResourceID,
s.po.value('ApproverID[1]', 'VARCHAR(20)') AS ApproverID,
s.po.value('VendorID[1]', 'VARCHAR(20)') AS VendorID,
s.po.value('DateSubmitted[1]', 'VARCHAR(20)') AS DateSubmitted,
s.po.value('CurrencyCode[1]', 'VARCHAR(20)') AS CurrencyCode,
s.po.value('TotalPOValue[1]', 'VARCHAR(20)') AS TotalPOValue,
po.pol.value('Description[1]', 'VARCHAR(20)') AS [Description],
po.pol.value('Rechargeable[1]', 'VARCHAR(20)') AS Rechargeable,
po.pol.value('Quantity[1]', 'VARCHAR(20)') AS Quantity,
po.pol.value('UnitPrice[1]', 'VARCHAR(20)') AS UnitPrice,
po.pol.value('LineTotal[1]', 'VARCHAR(20)') AS LineTotal
FROM @xml.nodes('/POSystem/ePurchaseOrder') AS s(po)
CROSS APPLY po.nodes('ePurchaseOrderLine') AS po(pol)[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 09:07:09

Oops. You want to CREATE a structure, not read one.
Post some sample data and expected output.

E 12°55'05.63"
N 56°04'39.26"

jamie1984
Starting Member

6 Posts

Posted - 2008-11-06 : 09:13:41

Thanks Peso, should I give you create table statements and some sample insert data?

jamie1984
Starting Member

6 Posts

Posted - 2008-11-06 : 10:29:50

OK,maybe this will simplify.

I have a view called "viewPO"

Heres an SP that selects some fields from that view based on a po number:


CREATE PROCEDURE [dbo].[sp_CreateMyXML]
@PONumber int

AS
SELECT 

[PoNumber],[PoOrderType],[POEmployeeNumber],[PoCostCenter],
[PoDivision],[POApproverNumber],[PODateRaised],
[PoJobNumber],[PoTotalValue],[VendorId]

FROM 
[viewPO] where PONumber = @PONumber 

FOR XML PATH('PurchaseOrder')

This when executed will return:

<PurchaseOrder>
  <PoNumber>919</PoNumber>
  <PoOrderType>Contract</PoOrderType>
  <POEmployeeNumber>0010009</POEmployeeNumber>
  <PoCostCenter>002</PoCostCenter>
  <PoDivision>XyZ</PoDivision>
  <POApproverNumber>0010008</POApproverNumber>
  <PODateRaised>2008-02-18T16:49:07</PODateRaised>
  <PoJobNumber>J000125</PoJobNumber>
  <PoTotalValue>4.00</PoTotalValue>
  <VendorId>RCN001</VendorId>
</PurchaseOrder>

What I require is the stored proc to also select the relevant po lines from view_POLines aswell. So I end up with this XML structure:


<POSystem>
 <PurchaseOrder>
  <PoNumber>919</PoNumber>
  <PoOrderType>Contract</PoOrderType>
  <POEmployeeNumber>0010009</POEmployeeNumber>
  <PoCostCenter>002</PoCostCenter>
  <PoDivision>XyZ</PoDivision>
  <POApproverNumber>0010008</POApproverNumber>
  <PODateRaised>2008-02-18T16:49:07</PODateRaised>
  <PoJobNumber>J000125</PoJobNumber>
  <PoTotalValue>37.00</PoTotalValue>
  <VendorId>RCN001</VendorId>
 

 <PurchaseOrderLine>
   <description>test</description>
   <qty>1</qty>
   <price>32</price>
 </PurchaseOrderLine>

 <PurchaseOrderLine>
   <description>test2</description>
   <qty>2</qty>
   <price>5</price>
 </PurchaseOrderLine>

 </PurchaseOrder>

</POSystem>

Does this make it any clearer?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:51:48

Try like this:-

CREATE PROCEDURE [dbo].[sp_CreateMyXML]
@PONumber int

AS
SELECT 

[PoNumber],[PoOrderType],[POEmployeeNumber],[PoCostCenter],
[PoDivision],[POApproverNumber],[PODateRaised],
[PoJobNumber],[PoTotalValue],[VendorId],pol.POLine
FROM 
[viewPO] v
CROSS APPLY(SELECT  description,qty,price 
            FROM view_POLines 
            WHERE PoNumber=v.PoNumber
            FOR XML PATH('PurchaseOrderLine'))pol(POLine)
where v.PONumber = @PONumber 
FOR XML PATH('PurchaseOrder')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 10:57:53

See http://weblogs.sqlteam.com/peterl/archive/2008/11/06/Create-nested-hierachy-XML.aspx

E 12°55'05.63"
N 56°04'39.26"

jamie1984
Starting Member

6 Posts

Posted - 2008-11-06 : 11:03:21

you are a genius and a life saver my friend.

thank you very much :-)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 11:20:49

Visakh, your suggestion puts in an extra tag named POLINE and misses the POSystem tag

<PurchaseOrder>
	<ID>1</ID>
	<Name>Peso</Name>
	<POLine>
		<PurchaseOrderLine>
			<ID>1</ID>
			<Value>Row 1 for Peso</Value>
		</PurchaseOrderLine>
		<PurchaseOrderLine>
			<ID>2</ID>
			<Value>Row 2 for Peso</Value>
		</PurchaseOrderLine>
	</POLine>
</PurchaseOrder>
<PurchaseOrder>
	<ID>2</ID>
	<Name>SQLTeam</Name>
	<POLine>
		<PurchaseOrderLine>
			<ID>3</ID>
			<Value>Row 1 for SQLTeam</Value>
		</PurchaseOrderLine>
	</POLine>
</PurchaseOrder>
<PurchaseOrder>
	<ID>3</ID>
	<Name>SQL</Name>
</PurchaseOrder>

My suggestion produces

<POSystem>
	<ePurchaseOrder>
		<MasterID>1</MasterID>
		<MasterName>Peso</MasterName>
		<ePurchaseOrderLine>
			<ChildID>1</ChildID>
			<ChildValue>Row 1 for Peso</ChildValue>
		</ePurchaseOrderLine>
		<ePurchaseOrderLine>
			<ChildID>2</ChildID>
			<ChildValue>Row 2 for Peso</ChildValue>
		</ePurchaseOrderLine>
	</ePurchaseOrder>
	<ePurchaseOrder>
		<MasterID>2</MasterID>
		<MasterName>SQLTeam</MasterName>
		<ePurchaseOrderLine>
			<ChildID>3</ChildID>
			<ChildValue>Row 1 for SQLTeam</ChildValue>
		</ePurchaseOrderLine>
	</ePurchaseOrder>
	<ePurchaseOrder>
		<MasterID>3</MasterID>
		<MasterName>SQL</MasterName>
		<ePurchaseOrderLine />
	</ePurchaseOrder>
</POSystem>

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources