Analisando XML digitado com um namespace padrão e nó raiz inconsistente

Os três problemas com o seu XQuery que posso ver são (e nada disso tem nada a ver com o XML digitado):

1. Você não está especificando o caminho correto para o /rootnó. Deveria ser:

   SELECT t.c.value('(.)[1]','varchar(50)') as type
   from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/transaction') as t(c)
   

2. O XML diferencia maiúsculas de minúsculas, então você precisa usar um "T" maiúsculo para o nó "Transação":

   SELECT t.c.value('(.)[1]','varchar(50)') as type
   from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/Transaction') as t(c)
   

3. Embora essas correções provavelmente recuperem 1 valor (neste caso, deve ser "SaleOnly"), ele não iterará pelos nós "item" porque você foi muito específico no caminho fornecido para a .nodes()função. Em vez disso, o nó final dessa especificação deve ser "item", que é o que você deseja iterar. E nesse caso, você move a parte "Transação" até a .value()função:

   SELECT t.c.value('(./Transaction)[1]','varchar(50)') as type
   from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)
   

Sobre a declaração de:

Eu trabalho em xml faltando e contendo a segunda declaração xmlns no elemento raiz, bem como com a estrutura reduzida para apenas raiz.

A "estrutura cortada para apenas raiz" deve ser manuseada removendo tudo entre o primeiro /e o /imediatamente anterior /root...(ou seja, o WebService/NewLeads/OutputSchema). Assim, o caminho resultante seria:

from @ixml.nodes('//root/item') as t(c)

OBSERVAÇÃO:
não consigo fazer isso funcionar 100% com o namespace declarado no <WebService>elemento ( consulte as notas adicionais, pois esse não é mais o caso ). Tirar isso faz funcionar. Dar um prefixo, como xmlns:something="http://www.orbis-software.com/WebSvcCon"fazê-lo funcionar, mas isso precisa ser declarado nos métodos. A única maneira de fazê-lo funcionar agora é declarando o namespace padrão em cada função XML ( .nodese .value) da seguinte forma:

SELECT t.c.value('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  (./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  /WebService/NewLeads/OutputSchema/root/item') as t(c)

NOTA 2:
Melhor ainda, você pode usar WITH XMLNAMESPACESpara declarar um ou mais namespaces para usar em toda a consulta, portanto não há necessidade de definir em cada função XML. Os dois a seguir funcionam:

;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)


;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

No entanto, lembre-se de que, se o documento não tiver o <WebService xmlns="http://www.orbis-software.com/WebSvcCon">elemento e, portanto, não tiver um namespace padrão, será necessário remover a ;WITH XMLNAMESPACESparte. Obviamente, se o <root>elemento tiver seu próprio namespace padrão, talvez você precise mantê-lo. Você pode brincar com ele até que funcione, agora que você conhece a conexão entre essas peças.

NOTA 3:
Se você acabar tendo dois namespaces padrão declarados -- um no <WebService>elemento e outro no <root>elemento -- então você precisa especificar o URI anotado <root xmlns="bob"> e a //sintaxe em vez do caminho totalmente qualificado. Portanto, se o seu XML se parecesse com:

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
 <NewLeads>
  <OutputSchema>
  <root xmlns="http://someplace" type="array">

Você então usaria:

;WITH XMLNAMESPACES (DEFAULT 'http://someplace')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

Mas isso não ajudará se você tiver o <WebService>elemento e ainda assim o <root>elemento não tiver a xmlnsdeclaração. Nesse caso, você ainda precisa especificar o namespace anotado no <WebService>elemento. Diversão diversão diversão :-).

NOTA 4: Ainda melhor: incorporando algo mencionado na resposta
de @wBob , podemos realmente nos livrar da cláusula e, em vez disso, usar um curinga de namespace. Você só precisa prefixar cada nó de cada função XML com . Agora a query deve ficar assim:;WITH XMLNAMESPACES*:

SELECT t.c.value('(./*:Transaction)[1]','varchar(50)') AS [type],
       t.c.value('(./*:SaleProperty/*:PostDistrict)[1]','varchar(50)') AS [PostDistrict]
FROM  @ixml.nodes('//*:root/*:item') t(c);

Isso significa que a consulta funciona em todos os seus cenários:

1. Estrutura completa começando com o nó "WebService", segunda declaração xmlns:

   <WebService xmlns="http://www.orbis-software.com/WebSvcCon">
     <NewLeads>
       <OutputSchema>
         <root xmlns="uri" type="array">
   

2. Estrutura completa começando com o nó "WebService", declaração xmlns única:

   <WebService xmlns="http://www.orbis-software.com/WebSvcCon">
     <NewLeads>
       <OutputSchema>
         <root type="array">
   

3. Estrutura reduzida começando com o nó "raiz", declaração xmlns única:

   <root xmlns="uri" type="array">
   

Se você deseja apenas despejar todos os valores de elemento como linhas, independentemente do namespace, pode usar um curinga de namespace, por exemplo:

DECLARE @xml XML = '<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
    <NewLeads>
        <OutputSchema>
            <root xmlns="" type="array">
                <item type="object">
                    <SaleProperty type="object">
                        <Type type="string">Freehold</Type>
                        <PostDistrict type="string">xxx</PostDistrict>
                        <Address type="string">address</Address>
                        <Value type="number">17.0</Value>
                    </SaleProperty>
                    <Transaction type="string">SaleOnly</Transaction>
                    <Quote type="object">
                        <Sale type="object">
                            <Fees type="number">450.0</Fees>
                            <Disbursements type="number">0.0</Disbursements>
                            <StampDuty type="number">0.0</StampDuty>
                            <Total type="number">450.0</Total>
                        </Sale>
                        <Discount type="number">0.0</Discount>
                        <VAT type="number">90.0</VAT>
                        <Total type="number">540.0</Total>
                    </Quote>
                    <MoverId type="number">12345678</MoverId>
                    <Name type="string">Mr AS</Name>
                    <Email type="string">[email protected]</Email>
                    <Telephone type="string">0123456789</Telephone>
                    <Comments type="string">Joint ownership</Comments>
                    <EstimatedMoveDate type="string">2015-11-25T05:57:00</EstimatedMoveDate>
                    <Charge type="number">4.99</Charge>
                    <ChargeStatus type="string">Chargeable</ChargeStatus>
                </item>
            </root>
        </OutputSchema>
    </NewLeads>
</WebService>'


-- Show all elements and their values irrespective of namespace
SELECT 
    x.y.value('local-name(..)', 'VARCHAR(MAX)') parentElementName,
    x.y.value('local-name(.)', 'VARCHAR(MAX)') elementName,
    x.y.value('.', 'VARCHAR(MAX)') elementValue
FROM @xml.nodes('//*[not(*)]') AS x(y)


SELECT 
    ws.c.value('local-name(..)', 'VARCHAR(MAX)') parentElementName,
    ws.c.value('local-name(.)', 'VARCHAR(MAX)') elementName,
    ws.c.value('.', 'VARCHAR(MAX)') elementValue
FROM @xml.nodes('*:WebService/*:NewLeads/*:OutputSchema/*:root/*:item/*[not(*)]') AS ws(c)

Resultado:

Você poderia então fazer algo como pivô dinâmico se precisar transformar isso em colunas. Se você deseja especificar elementos explicitamente, pode usar uma técnica semelhante.

A julgar pela sua pergunta, você provavelmente precisará gastar mais tempo aprendendo sobre namespaces XML, então comece aqui:

Adicionando namespaces usando WITH XMLNAMESPACES http://msdn.microsoft.com/en-us/library/ms177400.aspx