Russell_Harper
asked on
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:
<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.
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
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>
<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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.