Querying XML Data stored in SQL Server

Question

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>

Loc.City is not attribute. It's element.

Alexander Petrov
– Alexander Petrov

2017-04-07 15:20:06 +00:00
Commented Apr 7, 2017 at 15:20 — Alexander Petrov
– Alexander Petrov, Commented Apr 7, 2017 at 15:20
Ommit the @ sign in your XPath expression

PacoDePaco
– PacoDePaco

2017-04-07 15:21:35 +00:00
Commented Apr 7, 2017 at 15:21 — PacoDePaco
– PacoDePaco, Commented Apr 7, 2017 at 15:21
I still get the same error when I remove the @ sign.

SQLDev
– SQLDev

2017-04-07 15:34:32 +00:00
Commented Apr 7, 2017 at 15:34 — SQLDev
– SQLDev, Commented Apr 7, 2017 at 15:34

Gottfried Lesigang · Accepted Answer · 2017-04-11 13:09:10Z

1

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);

edited Apr 11, 2017 at 13:09

answered Apr 8, 2017 at 10:13

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SQLDev Over a year ago

When I used the third option I get the error - 'text()' is not supported on simple typed or 'w3.org/2001/XMLSchema#anyType' elements, found 'element({Namespace-Resume}:Loc.City,xs:string) *'.. I removed the [1] on both sides of the "=" sign and still got the error.

Gottfried Lesigang Over a year ago

@SQLDev Sorry, I don't know, where this is going wrong... If you copy my code as is, it should work. What did you change? Might be usefull, to use the edit option of your question and add some additional information.

SQLDev Over a year ago

If I use the code as is it works - no issues. But when I tweak it to select it from my table I get the error. This is the code I am using: DECLARE @location NVARCHAR(100)=N'Saginaw'; WITH XMLNAMESPACES(DEFAULT N'Namespace-Resume') SELECT Resume.value(N'(/Resume[(//Address/Addr.Location/Location/Loc.City/text())=sql:variable("@location")]/Name/Name.First/text())',N'nvarchar(max)') FROM JobCandidate

SQLDev Over a year ago

I am still getting the error - but I think I found the issue - the namespace is defined as schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume in my table. I think its because of the "/" present in the namespace name its throwing the error.

Gottfried Lesigang Over a year ago

@SQLDev I don't think so... See my update 2. It is actually not really important, how you define your namespace. But it must be the same for XML and query. If you cannot solve this with an explicit namespace, you might try the wildcard namespace like *:Element

|

Collectives™ on Stack Overflow

Querying XML Data stored in SQL Server

1 Answer 1

UPDATE: The SELECT you provide in comment

UPDATE 2: Namespace according to your comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE: The SELECT you provide in comment

UPDATE 2: Namespace according to your comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related