Solved

Read SOAP XML in SQL 2008

Posted on 2012-03-29
9
1,416 Views
Last Modified: 2012-03-29
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
Comment
Question by:Russell_Harper
  • 5
  • 2
  • 2
9 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37781313
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
 
LVL 20

Assisted Solution

by:BuggyCoder
BuggyCoder earned 250 total points
ID: 37781341
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
 

Author Comment

by:Russell_Harper
ID: 37781365
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
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37781389
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 37781395
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37781418
"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
 

Author Comment

by:Russell_Harper
ID: 37781511
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37781598
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37781633
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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

912 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now