spelen met XML
--------- QUERY withOUT XML nodes & EXIST
SELECT
Resume
.query('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.First)'
) FirstName
FROM
HumanResources
.JobCandidate
WHERE
Resume
.exist('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/Resume/Employment/Emp.OrgName[contains(.,"World")]'
) = 1
--------- VALUE
--------- FILTER via WHERE
SELECT
Resume
.value('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/EMail)[1]'
, 'VARCHAR(100)') as email
FROM
HumanResources
.JobCandidate
WHERE
LEN(Resume.value('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/EMail)[1]'
, 'VARCHAR(100)')) > 1
--------- VALUE
--------- Filter via EXIST
SELECT
Resume
.value('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.First)[1]'
, 'nvarchar(50)') FirstName
FROM
HumanResources
.JobCandidate
WHERE
Resume
.exist('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/Resume/Name/Name.Last[. != "Bassli"]'
) = 1
--------- QUERY withOUT XML nodes
SELECT
Resume
.query('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
data(/Resume/Name/Name.First)[1]'
) FirstName
FROM
HumanResources
.JobCandidate
WHERE
Resume
.exist('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/Resume/Name/Name.Last[. != "Bassli"]'
) = 1
--------- FOR/WHERE/RETURN
SELECT
JobCandidateID
,
Resume
.query('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $name in /Resume/Name
where data($name/Name.First) != "Shai"
return data($name/Name.Last)'
) LastName
FROM
HumanResources
.JobCandidate
-- /Resume/Employment/Emp.OrgName[contains(.,"World")]') = 1
SELECT
JobCandidateID
,
Resume
.query('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $employer in /Resume/Employment
where data($employer/Emp.IndustryCategory) = "Manufacturing"
return $employer/Emp.OrgName'
) Company
FROM
HumanResources
.JobCandidate
SELECT
JobCandidateID
,
Resume
.query('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $employer in /Resume/Employment
where data($employer/Emp.IndustryCategory) = "Manufacturing"
return data($employer/Emp.OrgName)'
) AS Company
FROM
HumanResources
.JobCandidate
---------FOR, RETURN, CONTAINS
SELECT
JobCandidateID
,
Resume
.query('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $employer in /Resume/Employment
return data($employer/Emp.OrgName)
'
) Company
FROM
HumanResources
.JobCandidate
WHERE
Resume
.exist('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/Resume/Employment/Emp.OrgName[contains(.,"World")]'
) = 1
--------- VALUE, EXIST, CONTAINS
SELECT
JobCandidateID
,
Resume
.value('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Employment/Emp.OrgName)[1]'
, 'VARCHAR(100)') Company
FROM
HumanResources
.JobCandidate
WHERE
Resume
.exist('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/Resume/Employment/Emp.OrgName[contains(.,"World")]'
) = 1
--------- QUERY, VALUE
SELECT
Resume
.query('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
data(/Resume/Name/Name.Last)'
) LastName
FROM
HumanResources
.JobCandidate
WHERE
Resume
.value('declare default element namespace"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
data(/Resume/Name/Name.First)[1]'
, 'varchar(50)') != 'Shai'