CODEWITHSUNDEEP
sql-serverxmlxml-namespacescross-apply
Ivica Čevis
Ivica Čevis

Reputation: 33

Query XML Data column using SQL and XML methods

I am performing a query against an XML data type column in SQL Server 2012. An example of the data is:

<ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">
  <ns:Address>
    <ns:Addr.Type>Home</ns:Addr.Type>
    <ns:Addr.Street>567 3rd Ave</ns:Addr.Street>
    <ns:Addr.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>MI </ns:Loc.State>
        <ns:Loc.City>Saginaw</ns:Loc.City>
      </ns:Location>
    </ns:Addr.Location>
    <ns:Addr.PostalCode>53900</ns:Addr.PostalCode>
  </ns:Address>
</ns:Resume>

I used this link to return First a last name, but now I want to return all candidates from Chicago and distinct states found in all resumes.

For all candidates from Chicago I use following code, but it always returns names of columns though the value exists. Can you help me?

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT 
JobCandidateID,
T.c.value('(ns:Name/ns:Name.First)[1]', 'nvarchar(100)') +N' '+
T.c.value('(ns:Name/ns:Name.Last)[1]', 'nvarchar(100)') as [First & Last Name],
T.c.value('(ns:Address/ns:Addr.Location/ns:Location/ns:Loc.City)[1]', 'nvarchar(100)') as [City Address],
T.c.value('(ns:Employment/ns:Emp.Location/ns:Location/ns:Loc.City)[1]', 'nvarchar(100)') as [City Employment]
FROM   
HumanResources.JobCandidate
CROSS APPLY
[Resume].nodes('/ns:Resume') AS T(c)
where [Resume].exist('/ns:Resume/ns:Address/ns:Addr.Location/ns:Location[ns:Loc.City="Chicago"]')=1;

Upvotes: 0

Views: 430

Answers (1)

Andrei T
Andrei T

Reputation: 44

For all candidates from Chicago :

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT 
JobCandidateID,
T.c.value('(ns:Name/ns:Name.First)[1]', 'nvarchar(100)') +N' '+
T.c.value('(ns:Name/ns:Name.Last)[1]', 'nvarchar(100)') as [First & Last Name],
T.c.value('(ns:Address/ns:Addr.Location/ns:Location/ns:Loc.City)[1]', 'nvarchar(100)') as [City Address],
T.c.value('(ns:Employment/ns:Emp.Location/ns:Location/ns:Loc.City)[1]', 'nvarchar(100)') as [City Employment]
FROM   
HumanResources.JobCandidate
CROSS APPLY
[Resume].nodes('/ns:Resume') AS T(c)
where [Resume].exist('/ns:Resume/ns:Employment/ns:Emp.Location/ns:Location[ns:Loc.City="Chicago"]')=1;

without cross apply :

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT 
JobCandidateID,
Resume.value('(/ns:Resume/ns:Name/ns:Name.First)[1]', 'nvarchar(100)') +N' '+
Resume.value('(/ns:Resume/ns:Name/ns:Name.Last)[1]', 'nvarchar(100)') as [First & Last Name],
Resume.value('(/ns:Resume/ns:Address/ns:Addr.Location/ns:Location/ns:Loc.City)[1]', 'nvarchar(100)') as [City Address],
Resume.value('(/ns:Resume/ns:Employment/ns:Emp.Location/ns:Location/ns:Loc.City)[1]', 'nvarchar(100)') as [City Employment]
FROM   
HumanResources.JobCandidate
where Resume.exist('/ns:Resume/ns:Employment/ns:Emp.Location/ns:Location[ns:Loc.City="Chicago"]')=1;

For distinct states found in all resumes :

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT distinct
Resume.value('(/ns:Resume/ns:Address/ns:Addr.Location/ns:Location/ns:Loc.State)[1]', 'nvarchar(100)') as [State]
FROM   
HumanResources.JobCandidate

Upvotes: 2

Related Questions