Query su campo xml

Tutte le risposte

• mercoledì 9 maggio 2012 20:28

  Moderatore

   

   
  

  0

  Come posso scrivere una query che mi restituisca tutti i record dove DynamicaData contiene key=MyFirstKey AND Value=MyFirstValue?
  

  Ciao,

  Potresti utilizzare il metodo exist del data type XML per eseguire una query simile al seguente esempio:

  USE tempdb;
  
  CREATE TABLE dbo.foo(
  c1 int NOT NULL,
  c2 xml NOT NULL
  );
  
  INSERT dbo.foo
  VALUES (1, '<DynamicProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WinTest"> 
    <AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> 
      <d2p1:KeyValueOfstringstring> 
        <d2p1:Key>MyFirstKey</d2p1:Key> 
        <d2p1:Value>MyFirstValue</d2p1:Value> 
      </d2p1:KeyValueOfstringstring> 
      <d2p1:KeyValueOfstringstring> 
        <d2p1:Key>MySecondKey</d2p1:Key> 
        <d2p1:Value>MySecondValue</d2p1:Value> 
      </d2p1:KeyValueOfstringstring> 
    </AllData> 
  </DynamicProfile>')
      , (2, '<DynamicProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WinTest"> 
    <AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> 
      <d2p1:KeyValueOfstringstring> 
        <d2p1:Key>MyFirstKey</d2p1:Key> 
        <d2p1:Value>MyFirstValue</d2p1:Value> 
      </d2p1:KeyValueOfstringstring> 
    </AllData> 
  </DynamicProfile>')
      , (3, '<DynamicProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WinTest"> 
    <AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> 
      <d2p1:KeyValueOfstringstring> 
        <d2p1:Key>MySecondKey</d2p1:Key> 
        <d2p1:Value>MySecondValue</d2p1:Value> 
      </d2p1:KeyValueOfstringstring> 
    </AllData> 
  </DynamicProfile>')
      , (4, '<DynamicProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WinTest"> 
    <AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> 
      <d2p1:KeyValueOfstringstring> 
        <d2p1:Key>MyFirstKey</d2p1:Key> 
      </d2p1:KeyValueOfstringstring> 
    </AllData> 
  </DynamicProfile>')
      , (5, '<DynamicProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WinTest"> 
    <AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> 
      <d2p1:KeyValueOfstringstring> 
        <d2p1:Value>MyFirstValue</d2p1:Value> 
      </d2p1:KeyValueOfstringstring> 
    </AllData> 
  </DynamicProfile>');
  
  --Come posso scrivere una query che mi restituisca tutti i record dove DynamicaData contiene key=MyFirstKey AND Value=MyFirstValue?
  WITH XMLNAMESPACES ('http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d2p1, DEFAULT 'http://schemas.datacontract.org/2004/07/WinTest')
  SELECT *
  FROM dbo.foo
  WHERE c2.exist('/DynamicProfile/AllData/d2p1:KeyValueOfstringstring/d2p1:Key[(text()[1]) = "MyFirstKey"]') = 1
  AND c2.exist('/DynamicProfile/AllData/d2p1:KeyValueOfstringstring/d2p1:Value[(text()[1]) = "MyFirstValue"]') = 1;
  
  /* Output:
  
  c1          c2
  ----------- -------------------
  1           <DynamicProfile...
  2           <DynamicProfile...
  
  (2 row(s) affected)
  
  */
  
  DROP TABLE dbo.foo;

  
  

  Ciao!

  ---

  Lorenzo Benaglia
  http://blogs.dotnethell.it/lorenzo
  http://social.technet.microsoft.com/Forums/it-IT/sqlserverit

  • Contrassegnato come risposta Andrea MontanariModerator mercoledì 9 maggio 2012 23:55
  •  
  • Risposta
  • Citazione