[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

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.
0
Russell_Harper
Asked:
Russell_Harper
1 Solution
 
Anthony PerkinsCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now