erasmo carlos erasmo carlos - 2 months ago 14
SQL Question

SQL: Select records that have XML tag

I would like to ask how to select only those records from a table that has a column with XML, that in their XML contain a specific tag.

Table name is: Searchindex
Column with XML: Record
XML element:

The tag is included only on a few records, not all.

XML sample structure:

<Record>
<ppn>asasaa</ppn>
<NAM>asdad</NAM>
<HasProduct>True</HasProduct> << this tag is only on certain records
<ART>asdadsa</ART>
<PublicationDate>06/21/1999</PublicationDate>
<PSal>1305</PSal>
<MSal>14</MSal>
<Xpos>False</Xpos>
</Record>


Any examples would be great.

Thanks a lot!

Answer

Here is example:

CREATE TABLE T
(
    ID int,
    X xml
)

INSERT INTO T VALUES
(1, '<Record>
  <ppn>asasaa</ppn>
  <NAM>asdad</NAM>  
  <HasProduct>True</HasProduct>
  <ART>asdadsa</ART>  
  <PublicationDate>06/21/1999</PublicationDate>
  <PSal>1305</PSal>
  <MSal>14</MSal>
  <Xpos>False</Xpos>
</Record>'),
(2, '<Record>
  <Xpos>False</Xpos>
</Record>')

SELECT *
FROM T
WHERE X.exist('Record/HasProduct')=1

/*Tag with value=True*/
SELECT *
FROM T
WHERE X.exist('Record/HasProduct[text()="True"]')=1