Daten in XML-Spalte suchen

Daten in XML-Spalte suchen

CREATE TABLE #mal
(
   id INT,
   n xml
)


INSERT INTO #mal (id, n) VALUES (1, '<dd><a>Micky</a><b>Maus</b></dd>') 
INSERT INTO #mal (id, n) VALUES (1, '<dd><a>Donald</a><b>Duck</b></dd>') 


SELECT *, n.value('(/dd/a)[1]', 'varchar(max)') FROM #mal
WHERE n.value('(/dd/a)[1]', 'varchar(max)') = 'Micky'


DROP TABLE #mal

für kompliziertere XMLs:

<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <Parameter>
     <Name>RETURN_VALUE</Name>
   </Parameter>
   <Parameter>
     <Name>FirstName</Name>
     <Value xsi:type="xsd:string">Tom</Value>
   </Parameter>
   <Parameter>
     <Name>LastName</Name>
     <Value xsi:type="xsd:string">Binggeli</Value>
   </Parameter>
   <Parameter>
     <Name>NationalityID</Name>
     <Value xsi:type="xsd:int">2</Value>
   </Parameter>
</Parameters>


select * from t_databaseaudit
where
parameters.exist('/Parameters/Parameter[Name="LastName"][Value="Binggeli"]')=1