CODEWITHSUNDEEP
sql-serverxml
curiousDev
curiousDev

Reputation: 437

How to fetch all value of an element from xml column in SQL

I have the following xml column in sql :

Create table dbo.sampleXML( ID int identity(1,1) primary key,
Name char(10) not null,
content xml null)
go

insert into dbo.sampleXML (Name,content) values ('ss',CAST('<Employee>
<FirstName> ss </FirstName>
<LastName> nn </LastName>
<Gender> Male </Gender>
<Age>31</Age>
</Employee>

' as xml))
insert into dbo.sampleXML (Name,content) values ('xx',CAST('<Employee>
<FirstName> xx </FirstName>
<LastName> yy </LastName>
<Gender> Male </Gender>
<Age>3</Age>
</Employee>

insert into dbo.sampleXML (Name,content) values ('dd',CAST('<Employee>
    <FirstName> ss </FirstName>
    <LastName> dd </LastName>
    <Gender> Female </Gender>
    <Age>30</Age>
    </Employee>

Tried to read all values from xml whose gender is male. Tried this

select content.query ('/Employee/FirstName') as FirstName,content.query('/Employee/Gender[1]') as Gender from dbo.sampleXML where content.value ('/Employee/Gender','varchar(10)')='Male'

But facing the error "select content.query ('/Employee/FirstName') as FirstName,content.query('/Employee/Gender[1]') as Gender from dbo.sampleXML where content.value ('/Employee/Gender','varchar(10)')='Male'"

Upvotes: 2

Views: 1174

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

From your question I take, that you want to filter your XML to show only male people. You achieve this with a filter predicate in .nodes().

Attention: The compare string is "(space)Male(space)" because your data is this way.

Create table dbo.sampleXML( ID int identity(1,1) primary key,
Name char(10) not null,
content xml null)
go

insert into dbo.sampleXML (Name,content) values ('ss',CAST('<Employee>
<FirstName> ss </FirstName>
<LastName> nn </LastName>
<Gender> Male </Gender>
<Age>31</Age>
</Employee>' as xml));
insert into dbo.sampleXML (Name,content) values ('xx',CAST('<Employee>
<FirstName> xx </FirstName>
<LastName> yy </LastName>
<Gender> Male </Gender>
<Age>3</Age>
</Employee>' as xml));
insert into dbo.sampleXML (Name,content) values ('dd',CAST('<Employee>
<FirstName> ss </FirstName>
<LastName> dd </LastName>
<Gender> Female </Gender>
<Age>30</Age>
</Employee>' as xml));

SELECT ID
      ,Filtered.Males.value('Gender[1]','varchar(max)') AS Gender 
      ,Filtered.Males.value('FirstName[1]','varchar(max)') AS FirstName 
      ,Filtered.Males.value('LastName[1]','varchar(max)') AS LastName
      ,Filtered.Males.value('Age[1]','int') AS Age
FROM dbo.sampleXML 
CROSS APPLY content.nodes('/Employee[Gender=" Male "]') AS Filtered(Males);

The result

ID  Gender  FirstName   LastName    Age
1    Male    ss           nn        31
2    Male    xx           yy        3

Upvotes: 3

har07
har07

Reputation: 89285

As the error message you should've seen suggests, value() XML method requires an XPath/XQuery that guaranteed to return singleton value or empty. /Employee/Gender[1] returns first Gender child in Employee, while it isn't guaranteed that there is only one Employee in the XML.

That said, you need to put index after Employee and Gender to guarantee a singleton or empty return value, or alternatively, wrap the entire XPath with parentheses and return only the first matched like this :

(/Employee/Gender)[1]

Furthermore, Gender elements value in your sample XML has whitespaces around so it won't be matched by 'Male' filter. You might want to use LTRIM(RTRIM()) to remove surrounding spaces since XPath normalize-space() isn't available in SQL Server XPath/XQuery.

Upvotes: 1

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

1) You need to specify the ordinal at each level of your xpath expression when you use .value

2) You have extra spaces inside the Gender literals, so you need to include them in the where clause, i.e. ' Male '

select content.query ('/Employee/FirstName') as FirstName,content.query('/Employee/Gender[1]') as Gender 
from dbo.sampleXML 
where content.value ('/Employee[1]/Gender[1]','varchar(10)')=' Male '

Upvotes: 3

Related Questions