How to extract XML node value?

Question

I'm trying to create an SQL function that returns comma separated value of all months that have the value true by reading the xmlNode.

However, I'm unable to navigate to the months node at all.

Adding namespace to the path hasn't helped me extract the value from Node.Data

For the first node (StartDateTime), I was able to extract the value on similar lines (by using the path as

/ScheduleDefinition/StartDateTime

Please refer the code below:

CREATE FUNCTION [dbo].[GetMonthsFromXML] 
(
    -- Add the parameters for the function here
    @xmlText NTEXT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
/*
<ScheduleDefinition>
<StartDateTime>04/10/2019 06:00:00</StartDateTime>
<MonthlyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer">
<Days>29</Days>
<MonthsOfYear>
<January>true</January>
<February>false</February>
<March>true</March>
<April>true</April>
<May>false</May>
<June>false</June>
<July>false</July>
<August>false</August>
<September>false</September>
<October>false</October>
<November>false</November>
<December>false</December>
</MonthsOfYear>
</MonthlyRecurrence>
</ScheduleDefinition>
*/
DECLARE @resultVar VARCHAR(MAX)
DECLARE @x xml
SET @x = CAST(@xmlText AS XML); 


;with cte 
as
(
SELECT 
    [MonthName] = Node.Data.value('local-name(.)', 'VARCHAR(20)')
    ,MonthValue = Node.Data.value('(.)[1]', 'VARCHAR(20)') 
FROM    
    @x.nodes('/ScheduleDefinition/MonthlyRecurrence/MonthsOfYear') Node(Data) 
 )
 select @resultVar = ISNULL( MAX(MonthValue),'') from cte; --where MonthValue is not null and MonthValue <> ''   ;

-- Return the result of the function
RETURN @resultVar

END
GO

Jan, Mar, Apr is the expected output for the above xml

score 0 · Answer 1 · answered Apr 17 '19 at 07:26

0

See https://learn.microsoft.com/en-us/sql/relational-databases/xml/add-namespaces-to-queries-with-with-xmlnamespaces?view=sql-server-2017, so along the lines of

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer' as rs)

SELECT 
    [MonthName] = Node.Data.value('local-name(.)', 'VARCHAR(20)')
    ,MonthValue = Node.Data.value('string(.)', 'VARCHAR(20)') 
FROM    
    @x.nodes('/ScheduleDefinition/rs:MonthlyRecurrence/rs:MonthsOfYear/rs:*[. = "true"]') Node(Data)

answered Apr 17 '19 at 07:26

Martin Honnen

160,499
6
90
110

Thank you for the solution and the url to documentaion. I learnt quite a bit. However I have run into another issue and I cannot get my head working for it. As there are several XML Definitions, I m trying to pass the path based on a recurrence type: For Example: x.nodes('sql:variable("@path")') Node(data) where @path contains the different pathValues. However, it doesn't seem to work – Madan Prabhakara Apr 18 '19 at 08:53
@MadanPrabhakara, ask a new question with the necessary details and the relevant tags (i.e. [tag:sql-server]). – Martin Honnen Apr 18 '19 at 10:08

How to extract XML node value?

1 Answers1