SQL 2008 results to XML multiple namespaces

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.
Russell_HarperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.