Solved

Read SOAP XML in SQL 2008

Posted on 2012-03-29
9
1,428 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
why sql server only update some statistics in the database ? 3 24
Create snapshot on MSSQL 2012 3 22
Sql Query 6 68
Sql query with where clause 2 17
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

825 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