Solved

SQL 2008 results to XML multiple namespaces

Posted on 2012-04-11
1
423 Views
Last Modified: 2012-07-11
I have some values in a temp table that I need to export in XML format.  The XML has multiple levels and namespaces which is throwing me off a bit.

This is the data set:
CREATE TABLE #shipments	(	OrderId int,
				dateShippedGMT datetime,
				carrierCode varchar(20),
				classCode varchar(20),
				trackingNumber varchar(50),
				shipmentCost numeric(17,2),
				shipmentTaxCost numeric(17,2))
							

declare @DeveloperKey varchar(100),
	@Password varchar(100),
	@accountID varchar(100)
		
select	@DeveloperKey = '1234567890',
	@Password = '9999',
	@accountID = '54321'	

INSERT #shipments
VALUES (123456, '2012-04-11T12:05:33', 'UPS', 'STD', '314159265358979323', 2.50, 0)

INSERT #shipments
VALUES (234567, '2012-04-11T12:06:13', 'UPS', 'STD', '314159265358979324', 1.50, 0)

SELECT * FROM #shipments
DROP TABLE #shipments

Open in new window

The XML format this needs to be exported to is:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://api.channeladvisor.com/webservices/">
   <soapenv:Header>
      <web:APICredentials>
         <web:DeveloperKey>1234567890</web:DeveloperKey>
         <web:Password>9999</web:Password>
      </web:APICredentials>
   </soapenv:Header>
   <soapenv:Body>
      <web:SubmitOrderShipmentList>
         <web:accountID>54321</web:accountID>
         <web:ShipmentList>
            <web:OrderShipmentList>
               <web:OrderShipment>
                  <web:OrderId>123456</web:OrderId>
                  <web:ShipmentType>Full</web:ShipmentType>
                  <web:FullShipment>
                     <web:dateShippedGMT>2012-04-11T12:05:33</web:dateShippedGMT>
                     <web:carrierCode>UPS</web:carrierCode>
                     <web:classCode>STD</web:classCode>
                     <web:trackingNumber>314159265358979323</web:trackingNumber>
                     <web:shipmentCost>2.50</web:shipmentCost>
                     <web:shipmentTaxCost>0</web:shipmentTaxCost>
                     <web:insuranceCost>0</web:insuranceCost>
                  </web:FullShipment>
               </web:OrderShipment>
               <web:OrderShipment>
                  <web:OrderId>234567</web:OrderId>
                  <web:ShipmentType>Full</web:ShipmentType>
                  <web:FullShipment>
                     <web:dateShippedGMT>2012-04-11T12:06:13</web:dateShippedGMT>
                     <web:carrierCode>UPS</web:carrierCode>
                     <web:classCode>STD</web:classCode>
                     <web:trackingNumber>314159265358979324</web:trackingNumber>
                     <web:shipmentCost>1.50</web:shipmentCost>
                     <web:shipmentTaxCost>0</web:shipmentTaxCost>
                     <web:insuranceCost>0</web:insuranceCost>
                  </web:FullShipment>
               </web:OrderShipment>               
            </web:OrderShipmentList>
         </web:ShipmentList>
      </web:SubmitOrderShipmentList>
   </soapenv:Body>
</soapenv:Envelope>

Open in new window

<web:DeveloperKey> needs to be populated with the variable @DeveloperKey
<web:Password> needs to be populated with the variable @Password
<web:accountID> needs to be populated with the variable @accountID
<web:dateShippedGMT> needs to be populated with #shipments.dateShippedGMT
<web:carrierCode> needs to be populated with #shipments.carrierCode
<web:classCode> needs to be populated with #shipments.classCode
<web:trackingNumber> needs to be populated with #shipments.trackingNumber
<web:shipmentCost> needs to be populated with #shipments.shipmentCost
<web:shipmentTaxCost> needs to be populated with #shipments.shipmentTaxCost

Any help would be appreciated.
0
Comment
Question by:Russell_Harper
1 Comment
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 37832252
While you may be able to use FOR XML EXPLICIT to create that Xml document, it has way too many hierarchies and you would be best advised to use something like .NET to do it.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question