Filtrar coluna XML no SQL Server

Para filtrar conteúdos

Gerando conteúdo

DECLARE @XmlData XML
SET @XmlData = N'<TransactionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <BasketHash>437e04ac543d10433e518a56246ad384</BasketHash>
  <Billing>
    <CustormerName>Fabian Corrêa Marques</CustormerName>
    <Email>test@mailinator.com</Email>
    <CelPhone>9999999999</CelPhone>
    <Login>test@mailinator.com</Login>
    <Identity Type="CPF">123456789</Identity>
    <BirthDate>1986-04-17T00:00:00</BirthDate>
    <Gender>M</Gender>
    <AddressLine>Rua Imaginária</AddressLine>
    <Number>123</Number>
    <Complement>Apartamento 103</Complement>
    <Neighbourhood>Centro</Neighbourhood>
    <City>Porto Alegre</City>
    <State>RS</State>
    <Country>BRA</Country>
    <PostalCode>123456789</PostalCode>
    <Phone>5555555555</Phone>
  </Billing>
  <Shipping>
    <CustormerName>Fabian Corrêa Marques</CustormerName>
    <Email>test@mailinator.com</Email>
    <CelPhone>9999999999</CelPhone>
    <Login>test@mailinator.com</Login>
    <Identity Type="CPF">123456789</Identity>
    <BirthDate>1986-04-17T00:00:00</BirthDate>
    <Gender>M</Gender>
    <AddressLine>Rua Imaginária</AddressLine>
    <Number>123</Number>
    <Complement>Apartamento 103</Complement>
    <Neighbourhood>Centro</Neighbourhood>
    <City>Porto Alegre</City>
    <State>RS</State>
    <Country>BRA</Country>
    <PostalCode>123456789</PostalCode>
    <Phone>5555555555</Phone>
  </Shipping>
  <Customer>
    <CreatedDate>2016-07-26T17:24:09.24</CreatedDate>
  </Customer>
  <WebSite>
    <WebSiteID>1</WebSiteID>
    <WebSiteUrl>https://www.correamarques.com.br/</WebSiteUrl>
  </WebSite>
  <Request>
    <UserAgent>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36</UserAgent>
  </Request>
  <PayableOrders>
    <OrderInfo>
      <OrderID>e09ccef8-f543-4eba-9679-9e31a7426e87</OrderID>
      <CustomerID>694013</CustomerID>
      <OrderNumber>CM-694013-00002</OrderNumber>
      <OrderDate>2017-03-27T14:39:02.4292-03:00</OrderDate>
      <Items>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985936</Identifier>
          <Description>Whey Protein Concentrado (1KG)</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">85.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985819</Identifier>
          <Description>BCAA (120 caps)</Description>
          <Quantity>2</Quantity>
          <UnitPrice Type="BRL">25.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>987772</Identifier>
          <Description>Albumina 500gr</Description>
          <Quantity>2</Quantity>
          <UnitPrice Type="BRL">33.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985925</Identifier>
          <Description>100% Batata Doce em pó (sabor natural) (1kg)</Description>
          <Quantity>2</Quantity>
          <UnitPrice Type="BRL">44.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985899</Identifier>
          <Description>Coqueteleira 3 compartimentos (com MixBall)</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">22.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Product</Type>
          <Identifier>985851</Identifier>
          <Description>Multivitaminico (120 caps) (nova fórmula)</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">33.000</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Delivery</Type>
          <Identifier>2</Identifier>
          <Description>PAC</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">23.34</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Discount</Type>
          <Identifier>76</Identifier>
          <Description>Desconto de 2,00%</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">6.880</UnitPrice>
        </ItemInfo>
        <ItemInfo>
          <Type>Discount</Type>
          <Identifier>2</Identifier>
          <Description>Desconto na entrega</Description>
          <Quantity>1</Quantity>
          <UnitPrice Type="BRL">23.34</UnitPrice>
        </ItemInfo>
      </Items>
      <ShopperTicketID>e8f0765b-a7fd-42ac-a5bd-b5125684d582</ShopperTicketID>
      <Total>337.12</Total>
      <PrepaidAmount>0</PrepaidAmount>
      <Properties>
        <PropertyInfo>
          <Name>utm_source</Name>
          <Value>buscape</Value>
        </PropertyInfo>
        <PropertyInfo>
          <Name>utm_medium</Name>
          <Value>cpc</Value>
        </PropertyInfo>
        <PropertyInfo>
          <Name>utm_campaign</Name>
          <Value>lowerfunnel</Value>
        </PropertyInfo>
      </Properties>
      <Seller>
        <SellerID>0</SellerID>
        <BankAgencyDigit xsi:nil="true" />
        <BankAccountDigit xsi:nil="true" />
        <CommissionAmount>0</CommissionAmount>
        <IsPercentageCommission>false</IsPercentageCommission>
      </Seller>
    </OrderInfo>
  </PayableOrders>
</TransactionInfo>'

Obter o valor da coluna do XML

SELECT @XmlData.value('(/TransactionInfo/PayableOrders/OrderInfo/Total)[1]','decimal(10,2)') AS PaypalTotalPaid

Fontes:

XQuery for the SQL Server DBA: XPath

http://www.allaboutmssql.com/2012/09/xqueryxpathxmlschemaxml-index_6.html
http://stackoverflow.com/questions/19940566/xquery-value-value-requires-a-singleton-or-empty-sequence-found-oper/22711296