Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Read SOAP XML in SQL 2008

Posted on 2012-03-29
9
Medium Priority
?
1,529 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 60

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 1000 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 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 60

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 60

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 60

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

886 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