SQL - Query value in XML field

1. Query data in XML field
2. Query data in XML field, condition on Node's Attribute
3. Query data in XML field, condition on Node's TEXT

Example


DECLARE @Messages TABLE (
   ID INTEGER IDENTITY(1, 1),
   [Timestamp] Timestamp,
   xData xml)


SET NOCOUNT ON


INSERT @Messages(xData)
VALUES (
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<baseElement xmlns="https://xml.abc.net/xmlws/services/v2/"> 
     <nodeElement attribute1="0106" attribute2="0003"> 
<node1 />
<node2>NODE2</node2>
     </nodeElement>
     <nodeElement attribute1="0101" attribute2="0004" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0101" attribute2="0005" /> 
</baseElement> 
</soap:Body>
</soap:Envelope>
')


INSERT @Messages(xData)
VALUES (
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<baseElement xmlns="https://xml.abc.net/xmlws/services/v2/"> 
     <nodeElement attribute1="0106" attribute2="0003"> 
<node1>I am node 1 in entry 2</node1>
<node2>I am node 2 in entry 2</node2>
     </nodeElement>
     <nodeElement attribute1="0101" attribute2="0004" />      
     <nodeElement attribute1="0106" attribute2="0004" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0101" attribute2="0005" /> 
</baseElement>
</soap:Body>
</soap:Envelope>
') 


INSERT @Messages(xData)
VALUES (
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<baseElement xmlns="https://xml.abc.net/xmlws/services/v2/">
     <nodeElement attribute1="0101" attribute2="0004" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0101" attribute2="0005" /> 
</baseElement>
</soap:Body>
</soap:Envelope>
')




;WITH XMLNAMESPACES
(
  'http://schemas.xmlsoap.org/soap/envelope/' AS soap,
  'https://xml.abc.net/xmlws/services/v2/' as hc
)


SELECT *
,xData.query('soap:Envelope/soap:Body/hc:baseElement/hc:nodeElement') as nodes
,xData.query('soap:Envelope/soap:Body/hc:baseElement/hc:nodeElement/hc:node1') as node1
FROM @Messages
-- Condition on ATTRIBUTE
WHERE xData.exist('soap:Envelope/soap:Body/hc:baseElement/hc:nodeElement[@attribute1="0106" and @attribute2="0004"]') = 1 
-- Condition on TEXT
or xData.exist('soap:Envelope/soap:Body/hc:baseElement/hc:nodeElement/hc:node2/text()[.="NODE2"]') = 1 

IIS7 and Apache on the same machine / Apache virtual host

Scenario

- Create DNS for local php site (mysite.localhost)
- Apache is installed on the same machine with IIS7
- The "mysite.localhost" want to be hosted separately, not under apache localhost, location is not under folder htdocs.

Analyse Solution

Since IIS and Apache are on same machine, there are 2 options to configure.
    a. Configure to use difference port. By default, IIS uses port 80, Apache can be configured using any.
        But, this solution can only make 2 web servers running on same machine, cannot resolve virtual hosting problem.
    b. Point or reassign IIS to listen to a new IP address, leave the default (127.0.0.1) for Apache. This solution is perfect satisfy the requirement.

Resolve problem

1. Point / assign new IP address for IIS

(Copy from http://www.iislogs.com/steveschofield/iis7-post-44-iis7-and-apache-on-the-same-machine)
1.1) Added or make sure your machine has two ip's
1. 2) Open a command prompt
1. 3) Type netsh
1. 4) Type http
1. 5) Type sho iplisten.  It should be blank
1.6) Type add iplisten ipaddress=127.0.0.2
You should get IP address successfully added
1. 7) Type sho iplisten again
It should sho  127.0.0.2  in the list
1. 8) Type exit to get out of netsh
1. 9) Type type netstat -an.  See if you notice  127.0.0.2 in the list.  If you see 0.0.0.0:80, do an iisreset

2. Configure Apache virtual host (..\apache\conf\extra\httpd-vhosts.conf)

NameVirtualHost 127.0.0.1:80
<VirtualHost *:80>
    ServerAdmin postmaster@dummy-host.localhost
    DocumentRoot "D:/projects/mysite"
    ServerName mysite
    ServerAlias www.mysite.localhost
    ErrorLog "logs/mysite.localhost-error.log"
    CustomLog "logs/mysite.localhost-access.log" combined
</VirtualHost>

3. Configure apache (..\apache\conf\httpd.conf)

3.1) Include virtual host file

# Virtual hosts
Include "conf/extra/httpd-vhosts.conf"
3.1) Access right to the folder of "mysite" 

<Directory "D:/projects/mysite">
   Options Indexes FollowSymLinks Includes ExecCGI
    AllowOverride All
    Order allow,deny
    Allow from all
</Directory>

4. Binding "mysite" host name to IP address 127.0.0.1

C:\Windows\System32\drivers\etc\hosts

127.0.0.1 mysite
127.0.0.1 www.mysite.localhost