• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1658
  • Last Modified:

Read SOAP XML in SQL 2008

I need to read this XML from a SQL table - what's the best way of doing this?

I don't have a table definition to populate at the moment so some indication on how to read different nodes and levels in the XML would be good.  So far I have tried the following - This returns a NULL value for OrderID:

DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <GetOrderListResponse xmlns="http://api.channeladvisor.com/webservices/">
      <GetOrderListResult>
        <Status>Success</Status>
        <MessageCode>0</MessageCode>
        <ResultData>
          <OrderResponseItem xmlns:q1="http://api.channeladvisor.com/datacontracts/orders" xsi:type="q1:OrderResponseDetailComplete">
            <q1:NumberOfMatches>2</q1:NumberOfMatches>
            <q1:OrderTimeGMT>2012-03-29T08:16:27.143</q1:OrderTimeGMT>
            <q1:LastUpdateDate>2012-03-29T08:17:11.1</q1:LastUpdateDate>
            <q1:TotalOrderAmount>35.9800</q1:TotalOrderAmount>
            <q1:OrderState>Active</q1:OrderState>
            <q1:DateCancelledGMT xsi:nil="true" />
            <q1:OrderID>15127888</q1:OrderID>
            <q1:ClientOrderIdentifier>15127888</q1:ClientOrderIdentifier>
            <q1:SellerOrderID />
            <q1:OrderStatus>
              <q1:CheckoutStatus>CompletedOffline</q1:CheckoutStatus>
              <q1:CheckoutDateGMT>2012-03-29T08:17:10</q1:CheckoutDateGMT>
              <q1:PaymentStatus>Cleared</q1:PaymentStatus>
              <q1:PaymentDateGMT>2012-03-29T08:17:10</q1:PaymentDateGMT>
              <q1:ShippingStatus>Unshipped</q1:ShippingStatus>
              <q1:ShippingDateGMT>1900-01-01T00:00:00</q1:ShippingDateGMT>
              <q1:OrderRefundStatus>NoRefunds</q1:OrderRefundStatus>
            </q1:OrderStatus>
            <q1:ResellerID />
            <q1:BuyerEmailAddress>test@test.com</q1:BuyerEmailAddress>
            <q1:EmailOptIn>false</q1:EmailOptIn>
            <q1:PaymentInfo>
              <q1:PaymentType />
              <q1:CreditCardLast4 />
              <q1:PayPalID />
              <q1:MerchantReferenceNumber />
              <q1:PaymentTransactionID />
            </q1:PaymentInfo>
            <q1:ShippingInfo>
              <q1:AddressLine1 />
              <q1:AddressLine2 />
              <q1:City />
              <q1:Region />
              <q1:RegionDescription />
              <q1:PostalCode />
              <q1:CountryCode>US</q1:CountryCode>
              <q1:CompanyName />
              <q1:JobTitle />
              <q1:Title />
              <q1:FirstName />
              <q1:LastName />
              <q1:Suffix />
              <q1:PhoneNumberDay />
              <q1:PhoneNumberEvening />
              <q1:ShipmentList>
                <q1:Shipment>
                  <q1:ShippingCarrier />
                  <q1:ShippingClass />
                  <q1:TrackingNumber />
                </q1:Shipment>
              </q1:ShipmentList>
              <q1:ShippingInstructions>None</q1:ShippingInstructions>
              <q1:EstimatedShipDate xsi:nil="true" />
              <q1:DeliveryDate xsi:nil="true" />
            </q1:ShippingInfo>
            <q1:BillingInfo>
              <q1:AddressLine1 />
              <q1:AddressLine2 />
              <q1:City />
              <q1:Region />
              <q1:RegionDescription />
              <q1:PostalCode />
              <q1:CountryCode />
              <q1:CompanyName />
              <q1:Title />
              <q1:FirstName />
              <q1:LastName />
              <q1:Suffix />
              <q1:PhoneNumberDay />
              <q1:PhoneNumberEvening />
            </q1:BillingInfo>
            <q1:FlagDescription />
            <q1:ShoppingCart>
              <q1:CartID>15127888</q1:CartID>
              <q1:CheckoutSource>Unspecified</q1:CheckoutSource>
              <q1:VATTaxCalculationOption>Unspecified</q1:VATTaxCalculationOption>
              <q1:VATShippingOption>Unspecified</q1:VATShippingOption>
              <q1:VATGiftWrapOption>Unspecified</q1:VATGiftWrapOption>
              <q1:LineItemSKUList>
                <q1:OrderLineItemItem xsi:type="q1:OrderLineItemItemResponse">
                  <q1:LineItemType>SKU</q1:LineItemType>
                  <q1:UnitPrice>17.9900</q1:UnitPrice>
                  <q1:LineItemID>16294837</q1:LineItemID>
                  <q1:AllowNegativeQuantity>false</q1:AllowNegativeQuantity>
                  <q1:Quantity>1</q1:Quantity>
                  <q1:ItemSaleSource>DIRECT_SALE</q1:ItemSaleSource>
                  <q1:SKU>0047000150BLU133</q1:SKU>
                  <q1:Title>Slimmer Straight Fit 470 Hard Torre Blue 30L</q1:Title>
                  <q1:BuyerUserID>test@test.com</q1:BuyerUserID>
                  <q1:BuyerFeedbackRating>0</q1:BuyerFeedbackRating>
                  <q1:SalesSourceID>72978022</q1:SalesSourceID>
                  <q1:VATRate>0</q1:VATRate>
                  <q1:TaxCost>0.0000</q1:TaxCost>
                  <q1:ShippingCost>0.0000</q1:ShippingCost>
                  <q1:ShippingTaxCost>0.0000</q1:ShippingTaxCost>
                  <q1:GiftWrapCost>0.0000</q1:GiftWrapCost>
                  <q1:GiftWrapTaxCost>0.0000</q1:GiftWrapTaxCost>
                  <q1:GiftMessage />
                  <q1:GiftWrapLevel />
                  <q1:RecyclingFee>0.0000</q1:RecyclingFee>
                  <q1:UnitWeight UnitOfMeasure="KG">0</q1:UnitWeight>
                  <q1:WarehouseLocation />
                  <q1:UserName />
                  <q1:DistributionCenterCode>BURY</q1:DistributionCenterCode>
                  <q1:IsFBA>false</q1:IsFBA>
                </q1:OrderLineItemItem>
                <q1:OrderLineItemItem xsi:type="q1:OrderLineItemItemResponse">
                  <q1:LineItemType>SKU</q1:LineItemType>
                  <q1:UnitPrice>17.9900</q1:UnitPrice>
                  <q1:LineItemID>16294838</q1:LineItemID>
                  <q1:AllowNegativeQuantity>false</q1:AllowNegativeQuantity>
                  <q1:Quantity>1</q1:Quantity>
                  <q1:ItemSaleSource>DIRECT_SALE</q1:ItemSaleSource>
                  <q1:SKU>0047000150BLU178</q1:SKU>
                  <q1:Title>Slimmer Straight Fit 470 Hard Torre Blue 28R</q1:Title>
                  <q1:BuyerUserID>test@test.com</q1:BuyerUserID>
                  <q1:BuyerFeedbackRating>0</q1:BuyerFeedbackRating>
                  <q1:SalesSourceID>72978023</q1:SalesSourceID>
                  <q1:VATRate>0</q1:VATRate>
                  <q1:TaxCost>0.0000</q1:TaxCost>
                  <q1:ShippingCost>0.0000</q1:ShippingCost>
                  <q1:ShippingTaxCost>0.0000</q1:ShippingTaxCost>
                  <q1:GiftWrapCost>0.0000</q1:GiftWrapCost>
                  <q1:GiftWrapTaxCost>0.0000</q1:GiftWrapTaxCost>
                  <q1:GiftMessage />
                  <q1:GiftWrapLevel />
                  <q1:RecyclingFee>0.0000</q1:RecyclingFee>
                  <q1:UnitWeight UnitOfMeasure="KG">0</q1:UnitWeight>
                  <q1:WarehouseLocation />
                  <q1:UserName />
                  <q1:DistributionCenterCode>BURY</q1:DistributionCenterCode>
                  <q1:IsFBA>false</q1:IsFBA>
                </q1:OrderLineItemItem>
              </q1:LineItemSKUList>
              <q1:LineItemInvoiceList>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>SalesTax</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>Shipping</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>ShippingInsurance</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>VATShipping</q1:LineItemType>
                  <q1:UnitPrice>0</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>GiftWrap</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>VATGiftWrap</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>RecyclingFee</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
              </q1:LineItemInvoiceList>
              <q1:LineItemPromoList>
                <q1:OrderLineItemPromo>
                  <q1:LineItemType>Promotion</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                  <q1:PromoCode />
                </q1:OrderLineItemPromo>
              </q1:LineItemPromoList>
            </q1:ShoppingCart>
            <q1:CustomValueList />
            <q1:BuyerIpAddress />
            <q1:TransactionNotes />
          </OrderResponseItem>
          <OrderResponseItem xmlns:q2="http://api.channeladvisor.com/datacontracts/orders" xsi:type="q2:OrderResponseDetailComplete">
            <q2:NumberOfMatches>2</q2:NumberOfMatches>
            <q2:OrderTimeGMT>2012-03-29T08:16:51.72</q2:OrderTimeGMT>
            <q2:LastUpdateDate>2012-03-29T08:17:11.1</q2:LastUpdateDate>
            <q2:TotalOrderAmount>17.9900</q2:TotalOrderAmount>
            <q2:OrderState>Active</q2:OrderState>
            <q2:DateCancelledGMT xsi:nil="true" />
            <q2:OrderID>15127889</q2:OrderID>
            <q2:ClientOrderIdentifier>15127889</q2:ClientOrderIdentifier>
            <q2:SellerOrderID />
            <q2:OrderStatus>
              <q2:CheckoutStatus>CompletedOffline</q2:CheckoutStatus>
              <q2:CheckoutDateGMT>2012-03-29T08:17:10</q2:CheckoutDateGMT>
              <q2:PaymentStatus>Cleared</q2:PaymentStatus>
              <q2:PaymentDateGMT>2012-03-29T08:17:10</q2:PaymentDateGMT>
              <q2:ShippingStatus>Unshipped</q2:ShippingStatus>
              <q2:ShippingDateGMT>1900-01-01T00:00:00</q2:ShippingDateGMT>
              <q2:OrderRefundStatus>NoRefunds</q2:OrderRefundStatus>
            </q2:OrderStatus>
            <q2:ResellerID />
            <q2:BuyerEmailAddress>test@test.com</q2:BuyerEmailAddress>
            <q2:EmailOptIn>false</q2:EmailOptIn>
            <q2:PaymentInfo>
              <q2:PaymentType />
              <q2:CreditCardLast4 />
              <q2:PayPalID />
              <q2:MerchantReferenceNumber />
              <q2:PaymentTransactionID />
            </q2:PaymentInfo>
            <q2:ShippingInfo>
              <q2:AddressLine1 />
              <q2:AddressLine2 />
              <q2:City />
              <q2:Region />
              <q2:RegionDescription />
              <q2:PostalCode />
              <q2:CountryCode>US</q2:CountryCode>
              <q2:CompanyName />
              <q2:JobTitle />
              <q2:Title />
              <q2:FirstName />
              <q2:LastName />
              <q2:Suffix />
              <q2:PhoneNumberDay />
              <q2:PhoneNumberEvening />
              <q2:ShipmentList>
                <q2:Shipment>
                  <q2:ShippingCarrier />
                  <q2:ShippingClass />
                  <q2:TrackingNumber />
                </q2:Shipment>
              </q2:ShipmentList>
              <q2:ShippingInstructions>None</q2:ShippingInstructions>
              <q2:EstimatedShipDate xsi:nil="true" />
              <q2:DeliveryDate xsi:nil="true" />
            </q2:ShippingInfo>
            <q2:BillingInfo>
              <q2:AddressLine1 />
              <q2:AddressLine2 />
              <q2:City />
              <q2:Region />
              <q2:RegionDescription />
              <q2:PostalCode />
              <q2:CountryCode />
              <q2:CompanyName />
              <q2:Title />
              <q2:FirstName />
              <q2:LastName />
              <q2:Suffix />
              <q2:PhoneNumberDay />
              <q2:PhoneNumberEvening />
            </q2:BillingInfo>
            <q2:FlagDescription />
            <q2:ShoppingCart>
              <q2:CartID>15127889</q2:CartID>
              <q2:CheckoutSource>Unspecified</q2:CheckoutSource>
              <q2:VATTaxCalculationOption>Unspecified</q2:VATTaxCalculationOption>
              <q2:VATShippingOption>Unspecified</q2:VATShippingOption>
              <q2:VATGiftWrapOption>Unspecified</q2:VATGiftWrapOption>
              <q2:LineItemSKUList>
                <q2:OrderLineItemItem xsi:type="q2:OrderLineItemItemResponse">
                  <q2:LineItemType>SKU</q2:LineItemType>
                  <q2:UnitPrice>17.9900</q2:UnitPrice>
                  <q2:LineItemID>16294839</q2:LineItemID>
                  <q2:AllowNegativeQuantity>false</q2:AllowNegativeQuantity>
                  <q2:Quantity>1</q2:Quantity>
                  <q2:ItemSaleSource>DIRECT_SALE</q2:ItemSaleSource>
                  <q2:SKU>0047000150BLU133</q2:SKU>
                  <q2:Title>Slimmer Straight Fit 470 Hard Torre Blue 30L</q2:Title>
                  <q2:BuyerUserID>test@test.com</q2:BuyerUserID>
                  <q2:BuyerFeedbackRating>0</q2:BuyerFeedbackRating>
                  <q2:SalesSourceID>72978024</q2:SalesSourceID>
                  <q2:VATRate>0</q2:VATRate>
                  <q2:TaxCost>0.0000</q2:TaxCost>
                  <q2:ShippingCost>0.0000</q2:ShippingCost>
                  <q2:ShippingTaxCost>0.0000</q2:ShippingTaxCost>
                  <q2:GiftWrapCost>0.0000</q2:GiftWrapCost>
                  <q2:GiftWrapTaxCost>0.0000</q2:GiftWrapTaxCost>
                  <q2:GiftMessage />
                  <q2:GiftWrapLevel />
                  <q2:RecyclingFee>0.0000</q2:RecyclingFee>
                  <q2:UnitWeight UnitOfMeasure="KG">0</q2:UnitWeight>
                  <q2:WarehouseLocation />
                  <q2:UserName />
                  <q2:DistributionCenterCode>BURY</q2:DistributionCenterCode>
                  <q2:IsFBA>false</q2:IsFBA>
                </q2:OrderLineItemItem>
              </q2:LineItemSKUList>
              <q2:LineItemInvoiceList>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>SalesTax</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>Shipping</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>ShippingInsurance</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>VATShipping</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>GiftWrap</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>VATGiftWrap</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>RecyclingFee</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
              </q2:LineItemInvoiceList>
              <q2:LineItemPromoList>
                <q2:OrderLineItemPromo>
                  <q2:LineItemType>Promotion</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                  <q2:PromoCode />
                </q2:OrderLineItemPromo>
              </q2:LineItemPromoList>
            </q2:ShoppingCart>
            <q2:CustomValueList />
            <q2:BuyerIpAddress />
            <q2:TransactionNotes />
          </OrderResponseItem>
        </ResultData>
      </GetOrderListResult>
    </GetOrderListResponse>
  </soap:Body>
</soap:Envelope>'


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT *
 FROM OPENXML(@hDoc, '/Envelope/Body/GetOrderListResponse/GetOrderListResult/ResultData/OrderResponseItem/',1)
 WITH (
	OrderID varchar(50) ' OrderID'
 )
 
EXEC sp_xml_removedocument @hDoc

Open in new window

0
Russell_Harper
Asked:
Russell_Harper
  • 5
  • 2
  • 2
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
I think the issue is the namespaces -- or lack thereof. For SQL 2008, I would also use the XML data type functions if by reading from table, you mean the SOAP results are in a column of type XML.

Here is an example:
DECLARE @your_table TABLE(
    ID BIGINT IDENTITY(1,1), 
    soapResult XML,
    PRIMARY KEY(ID)
);

INSERT INTO @your_table(soapResult)
VALUES (
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <GetOrderListResponse xmlns="http://api.channeladvisor.com/webservices/">
      <GetOrderListResult>
        <Status>Success</Status>
        <MessageCode>0</MessageCode>
        <ResultData>
          <OrderResponseItem xmlns:q1="http://api.channeladvisor.com/datacontracts/orders" xsi:type="q1:OrderResponseDetailComplete">
            <q1:NumberOfMatches>2</q1:NumberOfMatches>
            <q1:OrderTimeGMT>2012-03-29T08:16:27.143</q1:OrderTimeGMT>
            <q1:LastUpdateDate>2012-03-29T08:17:11.1</q1:LastUpdateDate>
            <q1:TotalOrderAmount>35.9800</q1:TotalOrderAmount>
            <q1:OrderState>Active</q1:OrderState>
            <q1:DateCancelledGMT xsi:nil="true" />
            <q1:OrderID>15127888</q1:OrderID>
            <q1:ClientOrderIdentifier>15127888</q1:ClientOrderIdentifier>
            <q1:SellerOrderID />
            <q1:OrderStatus>
              <q1:CheckoutStatus>CompletedOffline</q1:CheckoutStatus>
              <q1:CheckoutDateGMT>2012-03-29T08:17:10</q1:CheckoutDateGMT>
              <q1:PaymentStatus>Cleared</q1:PaymentStatus>
              <q1:PaymentDateGMT>2012-03-29T08:17:10</q1:PaymentDateGMT>
              <q1:ShippingStatus>Unshipped</q1:ShippingStatus>
              <q1:ShippingDateGMT>1900-01-01T00:00:00</q1:ShippingDateGMT>
              <q1:OrderRefundStatus>NoRefunds</q1:OrderRefundStatus>
            </q1:OrderStatus>
            <q1:ResellerID />
            <q1:BuyerEmailAddress>test@test.com</q1:BuyerEmailAddress>
            <q1:EmailOptIn>false</q1:EmailOptIn>
            <q1:PaymentInfo>
              <q1:PaymentType />
              <q1:CreditCardLast4 />
              <q1:PayPalID />
              <q1:MerchantReferenceNumber />
              <q1:PaymentTransactionID />
            </q1:PaymentInfo>
            <q1:ShippingInfo>
              <q1:AddressLine1 />
              <q1:AddressLine2 />
              <q1:City />
              <q1:Region />
              <q1:RegionDescription />
              <q1:PostalCode />
              <q1:CountryCode>US</q1:CountryCode>
              <q1:CompanyName />
              <q1:JobTitle />
              <q1:Title />
              <q1:FirstName />
              <q1:LastName />
              <q1:Suffix />
              <q1:PhoneNumberDay />
              <q1:PhoneNumberEvening />
              <q1:ShipmentList>
                <q1:Shipment>
                  <q1:ShippingCarrier />
                  <q1:ShippingClass />
                  <q1:TrackingNumber />
                </q1:Shipment>
              </q1:ShipmentList>
              <q1:ShippingInstructions>None</q1:ShippingInstructions>
              <q1:EstimatedShipDate xsi:nil="true" />
              <q1:DeliveryDate xsi:nil="true" />
            </q1:ShippingInfo>
            <q1:BillingInfo>
              <q1:AddressLine1 />
              <q1:AddressLine2 />
              <q1:City />
              <q1:Region />
              <q1:RegionDescription />
              <q1:PostalCode />
              <q1:CountryCode />
              <q1:CompanyName />
              <q1:Title />
              <q1:FirstName />
              <q1:LastName />
              <q1:Suffix />
              <q1:PhoneNumberDay />
              <q1:PhoneNumberEvening />
            </q1:BillingInfo>
            <q1:FlagDescription />
            <q1:ShoppingCart>
              <q1:CartID>15127888</q1:CartID>
              <q1:CheckoutSource>Unspecified</q1:CheckoutSource>
              <q1:VATTaxCalculationOption>Unspecified</q1:VATTaxCalculationOption>
              <q1:VATShippingOption>Unspecified</q1:VATShippingOption>
              <q1:VATGiftWrapOption>Unspecified</q1:VATGiftWrapOption>
              <q1:LineItemSKUList>
                <q1:OrderLineItemItem xsi:type="q1:OrderLineItemItemResponse">
                  <q1:LineItemType>SKU</q1:LineItemType>
                  <q1:UnitPrice>17.9900</q1:UnitPrice>
                  <q1:LineItemID>16294837</q1:LineItemID>
                  <q1:AllowNegativeQuantity>false</q1:AllowNegativeQuantity>
                  <q1:Quantity>1</q1:Quantity>
                  <q1:ItemSaleSource>DIRECT_SALE</q1:ItemSaleSource>
                  <q1:SKU>0047000150BLU133</q1:SKU>
                  <q1:Title>Slimmer Straight Fit 470 Hard Torre Blue 30L</q1:Title>
                  <q1:BuyerUserID>test@test.com</q1:BuyerUserID>
                  <q1:BuyerFeedbackRating>0</q1:BuyerFeedbackRating>
                  <q1:SalesSourceID>72978022</q1:SalesSourceID>
                  <q1:VATRate>0</q1:VATRate>
                  <q1:TaxCost>0.0000</q1:TaxCost>
                  <q1:ShippingCost>0.0000</q1:ShippingCost>
                  <q1:ShippingTaxCost>0.0000</q1:ShippingTaxCost>
                  <q1:GiftWrapCost>0.0000</q1:GiftWrapCost>
                  <q1:GiftWrapTaxCost>0.0000</q1:GiftWrapTaxCost>
                  <q1:GiftMessage />
                  <q1:GiftWrapLevel />
                  <q1:RecyclingFee>0.0000</q1:RecyclingFee>
                  <q1:UnitWeight UnitOfMeasure="KG">0</q1:UnitWeight>
                  <q1:WarehouseLocation />
                  <q1:UserName />
                  <q1:DistributionCenterCode>BURY</q1:DistributionCenterCode>
                  <q1:IsFBA>false</q1:IsFBA>
                </q1:OrderLineItemItem>
                <q1:OrderLineItemItem xsi:type="q1:OrderLineItemItemResponse">
                  <q1:LineItemType>SKU</q1:LineItemType>
                  <q1:UnitPrice>17.9900</q1:UnitPrice>
                  <q1:LineItemID>16294838</q1:LineItemID>
                  <q1:AllowNegativeQuantity>false</q1:AllowNegativeQuantity>
                  <q1:Quantity>1</q1:Quantity>
                  <q1:ItemSaleSource>DIRECT_SALE</q1:ItemSaleSource>
                  <q1:SKU>0047000150BLU178</q1:SKU>
                  <q1:Title>Slimmer Straight Fit 470 Hard Torre Blue 28R</q1:Title>
                  <q1:BuyerUserID>test@test.com</q1:BuyerUserID>
                  <q1:BuyerFeedbackRating>0</q1:BuyerFeedbackRating>
                  <q1:SalesSourceID>72978023</q1:SalesSourceID>
                  <q1:VATRate>0</q1:VATRate>
                  <q1:TaxCost>0.0000</q1:TaxCost>
                  <q1:ShippingCost>0.0000</q1:ShippingCost>
                  <q1:ShippingTaxCost>0.0000</q1:ShippingTaxCost>
                  <q1:GiftWrapCost>0.0000</q1:GiftWrapCost>
                  <q1:GiftWrapTaxCost>0.0000</q1:GiftWrapTaxCost>
                  <q1:GiftMessage />
                  <q1:GiftWrapLevel />
                  <q1:RecyclingFee>0.0000</q1:RecyclingFee>
                  <q1:UnitWeight UnitOfMeasure="KG">0</q1:UnitWeight>
                  <q1:WarehouseLocation />
                  <q1:UserName />
                  <q1:DistributionCenterCode>BURY</q1:DistributionCenterCode>
                  <q1:IsFBA>false</q1:IsFBA>
                </q1:OrderLineItemItem>
              </q1:LineItemSKUList>
              <q1:LineItemInvoiceList>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>SalesTax</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>Shipping</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>ShippingInsurance</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>VATShipping</q1:LineItemType>
                  <q1:UnitPrice>0</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>GiftWrap</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>VATGiftWrap</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
                <q1:OrderLineItemInvoice>
                  <q1:LineItemType>RecyclingFee</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                </q1:OrderLineItemInvoice>
              </q1:LineItemInvoiceList>
              <q1:LineItemPromoList>
                <q1:OrderLineItemPromo>
                  <q1:LineItemType>Promotion</q1:LineItemType>
                  <q1:UnitPrice>0.0000</q1:UnitPrice>
                  <q1:PromoCode />
                </q1:OrderLineItemPromo>
              </q1:LineItemPromoList>
            </q1:ShoppingCart>
            <q1:CustomValueList />
            <q1:BuyerIpAddress />
            <q1:TransactionNotes />
          </OrderResponseItem>
          <OrderResponseItem xmlns:q2="http://api.channeladvisor.com/datacontracts/orders" xsi:type="q2:OrderResponseDetailComplete">
            <q2:NumberOfMatches>2</q2:NumberOfMatches>
            <q2:OrderTimeGMT>2012-03-29T08:16:51.72</q2:OrderTimeGMT>
            <q2:LastUpdateDate>2012-03-29T08:17:11.1</q2:LastUpdateDate>
            <q2:TotalOrderAmount>17.9900</q2:TotalOrderAmount>
            <q2:OrderState>Active</q2:OrderState>
            <q2:DateCancelledGMT xsi:nil="true" />
            <q2:OrderID>15127889</q2:OrderID>
            <q2:ClientOrderIdentifier>15127889</q2:ClientOrderIdentifier>
            <q2:SellerOrderID />
            <q2:OrderStatus>
              <q2:CheckoutStatus>CompletedOffline</q2:CheckoutStatus>
              <q2:CheckoutDateGMT>2012-03-29T08:17:10</q2:CheckoutDateGMT>
              <q2:PaymentStatus>Cleared</q2:PaymentStatus>
              <q2:PaymentDateGMT>2012-03-29T08:17:10</q2:PaymentDateGMT>
              <q2:ShippingStatus>Unshipped</q2:ShippingStatus>
              <q2:ShippingDateGMT>1900-01-01T00:00:00</q2:ShippingDateGMT>
              <q2:OrderRefundStatus>NoRefunds</q2:OrderRefundStatus>
            </q2:OrderStatus>
            <q2:ResellerID />
            <q2:BuyerEmailAddress>test@test.com</q2:BuyerEmailAddress>
            <q2:EmailOptIn>false</q2:EmailOptIn>
            <q2:PaymentInfo>
              <q2:PaymentType />
              <q2:CreditCardLast4 />
              <q2:PayPalID />
              <q2:MerchantReferenceNumber />
              <q2:PaymentTransactionID />
            </q2:PaymentInfo>
            <q2:ShippingInfo>
              <q2:AddressLine1 />
              <q2:AddressLine2 />
              <q2:City />
              <q2:Region />
              <q2:RegionDescription />
              <q2:PostalCode />
              <q2:CountryCode>US</q2:CountryCode>
              <q2:CompanyName />
              <q2:JobTitle />
              <q2:Title />
              <q2:FirstName />
              <q2:LastName />
              <q2:Suffix />
              <q2:PhoneNumberDay />
              <q2:PhoneNumberEvening />
              <q2:ShipmentList>
                <q2:Shipment>
                  <q2:ShippingCarrier />
                  <q2:ShippingClass />
                  <q2:TrackingNumber />
                </q2:Shipment>
              </q2:ShipmentList>
              <q2:ShippingInstructions>None</q2:ShippingInstructions>
              <q2:EstimatedShipDate xsi:nil="true" />
              <q2:DeliveryDate xsi:nil="true" />
            </q2:ShippingInfo>
            <q2:BillingInfo>
              <q2:AddressLine1 />
              <q2:AddressLine2 />
              <q2:City />
              <q2:Region />
              <q2:RegionDescription />
              <q2:PostalCode />
              <q2:CountryCode />
              <q2:CompanyName />
              <q2:Title />
              <q2:FirstName />
              <q2:LastName />
              <q2:Suffix />
              <q2:PhoneNumberDay />
              <q2:PhoneNumberEvening />
            </q2:BillingInfo>
            <q2:FlagDescription />
            <q2:ShoppingCart>
              <q2:CartID>15127889</q2:CartID>
              <q2:CheckoutSource>Unspecified</q2:CheckoutSource>
              <q2:VATTaxCalculationOption>Unspecified</q2:VATTaxCalculationOption>
              <q2:VATShippingOption>Unspecified</q2:VATShippingOption>
              <q2:VATGiftWrapOption>Unspecified</q2:VATGiftWrapOption>
              <q2:LineItemSKUList>
                <q2:OrderLineItemItem xsi:type="q2:OrderLineItemItemResponse">
                  <q2:LineItemType>SKU</q2:LineItemType>
                  <q2:UnitPrice>17.9900</q2:UnitPrice>
                  <q2:LineItemID>16294839</q2:LineItemID>
                  <q2:AllowNegativeQuantity>false</q2:AllowNegativeQuantity>
                  <q2:Quantity>1</q2:Quantity>
                  <q2:ItemSaleSource>DIRECT_SALE</q2:ItemSaleSource>
                  <q2:SKU>0047000150BLU133</q2:SKU>
                  <q2:Title>Slimmer Straight Fit 470 Hard Torre Blue 30L</q2:Title>
                  <q2:BuyerUserID>test@test.com</q2:BuyerUserID>
                  <q2:BuyerFeedbackRating>0</q2:BuyerFeedbackRating>
                  <q2:SalesSourceID>72978024</q2:SalesSourceID>
                  <q2:VATRate>0</q2:VATRate>
                  <q2:TaxCost>0.0000</q2:TaxCost>
                  <q2:ShippingCost>0.0000</q2:ShippingCost>
                  <q2:ShippingTaxCost>0.0000</q2:ShippingTaxCost>
                  <q2:GiftWrapCost>0.0000</q2:GiftWrapCost>
                  <q2:GiftWrapTaxCost>0.0000</q2:GiftWrapTaxCost>
                  <q2:GiftMessage />
                  <q2:GiftWrapLevel />
                  <q2:RecyclingFee>0.0000</q2:RecyclingFee>
                  <q2:UnitWeight UnitOfMeasure="KG">0</q2:UnitWeight>
                  <q2:WarehouseLocation />
                  <q2:UserName />
                  <q2:DistributionCenterCode>BURY</q2:DistributionCenterCode>
                  <q2:IsFBA>false</q2:IsFBA>
                </q2:OrderLineItemItem>
              </q2:LineItemSKUList>
              <q2:LineItemInvoiceList>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>SalesTax</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>Shipping</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>ShippingInsurance</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>VATShipping</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>GiftWrap</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>VATGiftWrap</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
                <q2:OrderLineItemInvoice>
                  <q2:LineItemType>RecyclingFee</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                </q2:OrderLineItemInvoice>
              </q2:LineItemInvoiceList>
              <q2:LineItemPromoList>
                <q2:OrderLineItemPromo>
                  <q2:LineItemType>Promotion</q2:LineItemType>
                  <q2:UnitPrice>0.0000</q2:UnitPrice>
                  <q2:PromoCode />
                </q2:OrderLineItemPromo>
              </q2:LineItemPromoList>
            </q2:ShoppingCart>
            <q2:CustomValueList />
            <q2:BuyerIpAddress />
            <q2:TransactionNotes />
          </OrderResponseItem>
        </ResultData>
      </GetOrderListResult>
    </GetOrderListResponse>
  </soap:Body>
</soap:Envelope>'
);

;WITH XMLNAMESPACES(
    'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
    'http://api.channeladvisor.com/datacontracts/orders' AS q1,
    DEFAULT 'http://api.channeladvisor.com/webservices/'
)
SELECT T.*, OrderID=S.R.value('.', 'VARCHAR(15)')
FROM @your_table T
CROSS APPLY soapResult.nodes('/soap:Envelope/soap:Body/GetOrderListResponse/GetOrderListResult/ResultData/OrderResponseItem/q1:OrderID') S(R)
; 

Open in new window

0
 
BuggyCoderCommented:
Here is the query to read status and message code, try modifying the same as per your requirements:-

with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as [soap],
                    default 'http://api.channeladvisor.com/webservices/')

SELECT XMLParams.ID.value('Status[1]','varchar(20)') as 'Status',
XMLParams.ID.value('MessageCode[1]','varchar(20)') as 'MessageCode'
from @xml.nodes('/soap:Envelope/soap:Body/GetOrderListResponse/GetOrderListResult') as XMLParams(ID);


Just using bit of XQuery
0
 
Russell_HarperAuthor Commented:
Would I have to create multiple CROSS APPLY joins to get different nodes at the same and/or sub levels?  Lets say I wanted to see:

<q1:OrderID>
<q1:CheckoutDateGMT>
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BuggyCoderCommented:
Yes you have to, suggest you study the article below in detail:-
http://msdn.microsoft.com/en-us/library/ms345115%28v=sql.90%29.aspx
0
 
Kevin CrossChief Technology OfficerCommented:
No, you would not need to use multiple CROSS APPLYs, you can simply use XPATH to get to the other values.

;WITH XMLNAMESPACES(
    'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
    'http://api.channeladvisor.com/datacontracts/orders' AS q1,
    DEFAULT 'http://api.channeladvisor.com/webservices/'
)
SELECT OrderID=S.R.value('q1:OrderID[1]', 'VARCHAR(15)')
     , CheckoutDateGMT=S.R.value('q1:OrderStatus[1]/q1:CheckoutDateGMT[1]', 'DATETIME')
     /* Alternative using CONVERT() format code 126: http://msdn.microsoft.com/en-us/library/ms187928.aspx */
     --, CONVERT(DATETIME, S.R.value('q1:OrderStatus[1]/q1:CheckoutDateGMT[1]', 'VARCHAR(25)'), 126)
FROM @your_table T
CROSS APPLY soapResult.nodes('/soap:Envelope/soap:Body/GetOrderListResponse/GetOrderListResult/ResultData/OrderResponseItem') S(R)
;
0
 
Kevin CrossChief Technology OfficerCommented:
"Yes you have to, suggest you study the article" << This is incorrect and the referenced article disproves it in the "Example: Fetching Properties of an Object" section.

The code I provided WILL work and results in:
OrderID         CheckoutDateGMT
--------------- -----------------------
15127888        2012-03-29 08:17:10.000
15127889        2012-03-29 08:17:10.000

Open in new window

0
 
Russell_HarperAuthor Commented:
There are potentially multiple items within <q1:LineItemSKUList> - How would I read these?  Using the above it only returns the 1st item for each order
0
 
Kevin CrossChief Technology OfficerCommented:
That is where CROSS APPLY comes into play.
EDIT: Actually, if you set the focus on the q1:LineItemSKUList in the first place, you can use the parent axis to get back to the OrderID, and other elements that are above these values. Therefore, this can be accomplished similar to what you already have.
0
 
Kevin CrossChief Technology OfficerCommented:
For example:
;WITH XMLNAMESPACES(
    'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
    'http://api.channeladvisor.com/datacontracts/orders' AS q1,
    DEFAULT 'http://api.channeladvisor.com/webservices/'
)
SELECT OrderID=S.R.value('../../../q1:OrderID[1]', 'VARCHAR(15)')
     , CheckoutDateGMT=S.R.value('../../../q1:OrderStatus[1]/q1:CheckoutDateGMT[1]', 'DATETIME')
     , LineItemID=S.R.value('q1:LineItemID[1]', 'VARCHAR(100)')
FROM @your_table T
CROSS APPLY soapResult.nodes('/soap:Envelope/soap:Body/GetOrderListResponse/GetOrderListResult/ResultData/OrderResponseItem/q1:ShoppingCart/q1:LineItemSKUList/q1:OrderLineItemItem') S(R)
;

Try to get the rest from that. If you have any difficulties, you can use the Ask Related Question link and it will increase the chance that you get both new and old (those of us here) participants.

I hope that helps!

Best regards and happy coding,

Kevin
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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