CODEWITHSUNDEEP
sql-serverxml
SQLDev
SQLDev

Reputation: 65

Querying XML Data stored in SQL Server

I have a column called Resume of type XML stored in a table in MS-SQL Server.

I want to retrieve all the candidates who are from the city Saginaw.

When I use the query 

SELECT Resume.query('(: explicit namespace :)declare namespace ns="Namespace-Resume";
                //ns:Address/ns:Addr.Location/ns:Location/ns:Loc.City')
FROM JobCandidate

I get all the values for the city which is fine but when I use the following query:

SELECT Resume.query('(: explicit namespace :)declare namespace ns="Namespace-Resume";
                //ns:Location[@ns:Loc.City="Saginaw"]/ns:Name')
FROM JobCandidate

I get an error "There is not attribute named Loc.City".

The sample data is as below:

<ns:Resume xmlns:ns="Namespace-Resume">
<ns:Name>
    <ns:Name.Prefix></ns:Name.Prefix>
    <ns:Name.First>Shai</ns:Name.First>
    <ns:Name.Middle></ns:Name.Middle>
    <ns:Name.Last>Bassli</ns:Name.Last>
    <ns:Name.Suffix></ns:Name.Suffix>
  </ns:Name>
<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:Address>
</ns:Resume>

Upvotes: 1

Views: 140

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

Your question is not all clear... I'll provide several approaches, one of them will hopefully point you the way (You can copy the whole lot into a query window and execute it stand-alone):

DECLARE @xml XML=
N'<ns:Resume xmlns:ns="Namespace-Resume">
  <ns:Name>
    <ns:Name.Prefix />
    <ns:Name.First>Shai</ns:Name.First>
    <ns:Name.Middle />
    <ns:Name.Last>Bassli</ns:Name.Last>
    <ns:Name.Suffix />
  </ns:Name>
  <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:Address>
</ns:Resume>';

--Read one element's text with namespaces wildcards

SELECT @xml.value(N'(/*:Resume/*:Name/*:Name.First/text())[1]',N'nvarchar(max)');

--Use a default namespace

WITH XMLNAMESPACES(DEFAULT N'Namespace-Resume')
SELECT @xml.value(N'(/Resume/Address/Addr.Location/Location/Loc.City/text())[1]',N'nvarchar(max)');

--Your sample XML includes one person only, but I assume there are more --Use a predicate to get the name for a given location

DECLARE @location NVARCHAR(100)=N'Saginaw';--Change this for tests
WITH XMLNAMESPACES(DEFAULT N'Namespace-Resume')
SELECT @xml.value(N'(/Resume[(Address/Addr.Location/Location/Loc.City/text())[1]=sql:variable("@location")]/Name/Name.First/text())[1]',N'nvarchar(max)');

--Read several values of this node --Use a predicate to get the name for a given location

WITH XMLNAMESPACES(DEFAULT N'Namespace-Resume')
SELECT r.value(N'(Name/Name.First/text())[1]',N'nvarchar(max)')
      ,r.value(N'(Name/Name.Last/text())[1]',N'nvarchar(max)') 
FROM @xml.nodes(N'/Resume[(Address/Addr.Location/Location/Loc.City/text())[1]=sql:variable("@location")]') AS A(r);

UPDATE: The SELECT you provide in comment

The call to nodes() is missing? Try it like this:

DECLARE @location NVARCHAR(100)=N'Saginaw'; 
WITH XMLNAMESPACES(DEFAULT N'Namespace-Resume') 
SELECT r.value(N'(Name/Name.First/text())[1]',N'nvarchar(max)')
      ,r.value(N'(Name/Name.Last/text())[1]',N'nvarchar(max)') 
FROM JobCandidate
CROSS APPLY Resume.nodes(N'/Resume[(Address/Addr.Location/Location/Loc.City/text())[1]=sql:variable("@location")]') AS A(r);

UPDATE 2: Namespace according to your comment

DECLARE @xml XML=
N'<ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">
  <ns:Name>
    <ns:Name.Prefix />
    <ns:Name.First>Shai</ns:Name.First>
    <ns:Name.Middle />
    <ns:Name.Last>Bassli</ns:Name.Last>
    <ns:Name.Suffix />
  </ns:Name>
  <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:Address>
</ns:Resume>';
DECLARE @location NVARCHAR(100)=N'Saginaw';--Change this for tests

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')
SELECT r.value(N'(Name/Name.First/text())[1]',N'nvarchar(max)')
      ,r.value(N'(Name/Name.Last/text())[1]',N'nvarchar(max)') 
FROM @xml.nodes(N'/Resume[(Address/Addr.Location/Location/Loc.City/text())[1]=sql:variable("@location")]') AS A(r);

Upvotes: 1

Related Questions