Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Read SOAP XML in SQL 2008

Posted on 2012-03-29
9
Medium Priority
?
1,499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 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