MSSQL - XQuery Example

http://www.sqlservercentral.com/articles/Stairway+Series/The+XML+exist()+and+nodes()+Methods/92785/


-- qcreating sample table
USE master;
GO

IF DB_ID('ClientDB') IS NOT NULL
DROP DATABASE ClientDB;
GO

CREATE DATABASE ClientDB;
GO

USE ClientDB;
GO

IF OBJECT_ID('ClientInfoCollection') IS NOT NULL
DROP XML SCHEMA COLLECTION ClientInfoCollection;
GO

CREATE XML SCHEMA COLLECTION ClientInfoCollection AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="urn:ClientInfoNamespace"
targetNamespace="urn:ClientInfoNamespace"
elementFormDefault="qualified">
  <xsd:element name="People">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Person" minOccurs="1" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="FirstName" type="xsd:string" minOccurs="1" maxOccurs="1" />
              <xsd:element name="LastName" type="xsd:string" minOccurs="1" maxOccurs="1" />
              <xsd:element name="FavoriteBook" type="xsd:string" minOccurs="0" maxOccurs="5" />
            </xsd:sequence>
            <xsd:attribute name="id" type="xsd:integer" use="required"/>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>';
GO

IF OBJECT_ID('ClientInfo') IS NOT NULL
DROP TABLE ClientInfo;
GO

CREATE TABLE ClientInfo
(
  ClientID INT PRIMARY KEY IDENTITY,
  Info_untyped XML,
  Info_typed XML(ClientInfoCollection)
);

INSERT INTO ClientInfo (Info_untyped, Info_typed)
VALUES
(
  '<?xml version="1.0" encoding="UTF-8"?>
  <People>
    <Person id="1234">
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
    </Person>
    <Person id="5678">
      <FirstName>Jane</FirstName>
      <LastName>Doe</LastName>
    </Person>
 </People>',
  '<?xml version="1.0" encoding="UTF-8"?>
  <People xmlns="urn:ClientInfoNamespace">
    <Person id="1234">
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
    </Person>
    <Person id="5678">
      <FirstName>Jane</FirstName>
      <LastName>Doe</LastName>
    </Person>
  </People>'
);


-- Verifying the existence of a specific element
SELECT
  Info_untyped.exist(
    '/People/Person[FirstName="Jane"]'),
  Info_typed.exist(
    'declare namespace ns="urn:ClientInfoNamespace";
   /ns:People/ns:Person[ns:FirstName="Jane"]')
FROM ClientInfo;


-- query with sql values
SELECT
  ClientID,
  Info_untyped.value(
    'concat((/People/Person[@id=5678]/FirstName)[1], " ",
      (/People/Person[@id=5678]/LastName)[1])',
    'varchar(25)') AS FullName
FROM ClientInfo
WHERE
  Info_untyped.exist(
    '/People/Person[@id=5678]') = 1;


-- query with sql variables
DECLARE @id INT;
SET @id = 5678;
SELECT
  ClientID,
  Info_untyped.value(
    'concat((/People/Person[@id=sql:variable("@id")]/FirstName)[1],
      " ", (/People/Person[@id=sql:variable("@id")]/LastName)[1])',
    'varchar(25)') AS FullName
FROM ClientInfo
WHERE
  Info_untyped.exist(
    '/People/Person[@id=sql:variable("@id")]') = 1;