Get values from xml table column

Question

I have a table with XML column as shown below.

<option>
            <OptionName>Option 1</OptionName>
            <grant>
                            <GrantName>Grant 1</Name>
                            <schedules>
                                            <schedule>
                                                            <scheduleID></ID>
                                                            <scheduleName></Name>
                                                            <scheduleDate>1/1/2018</Date>
                                                            <scheduleAmount></Amount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></ID>
                                                            <scheduleName></Name>
                                                            <scheduleDate>2/1/2018</Date>
                                                            <scheduleAmount></Amount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></ID>
                                                            <scheduleName></Name>
                                                            <scheduleDate>3/1/2018</Date>
                                                            <scheduleAmount></Amount>
                                            </schedule>
                            </schedules>
            </grant>
            <grant>
                            <GrantName>Grant 2</Name>
                            <schedules>
                                            <schedule>
                                                            <scheduleID></ID>
                                                            <scheduleName></Name>
                                                            <scheduleDate>1/1/2019</Date>
                                                            <scheduleAmount></Amount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></ID>
                                                            <scheduleName></Name>
                                                            <scheduleDate>2/1/2019</Date>
                                                            <scheduleAmount></Amount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></ID>
                                                            <scheduleName></Name>
                                                            <scheduleDate>3/1/2019</Date>
                                                            <scheduleAmount></Amount>
                                            </schedule>
                            </schedules>
            </grant>

And I want to run the query on table to get the data in below format.

OptionName | GrantName | ScheduleDate    

Option 1   | Grant 1   | 1/1/2018    
Option 1   | Grant 1   | 2/1/2018    
Option 1   | Grant 1   | 3/1/2018    
Option 1   | Grant 2   | 1/1/2019    
Option 1   | Grant 2   | 2/1/2019    
Option 1   | Grant 2   | 3/1/2019    

Below is the query which i am trying. but it doesn't give all the value if the child node. Since i have to specify the index as [1]

select 
   FactChange.Fact.value('(Option/OptionName)[1]','varchar(max)') OptionName
   ,FactChange.Fact.value('(Option/Grant/GrantName)[1]', 'varchar(max)') grantName
from FactChange(nolock) 

Answer 1

I've made some changes in your XML, mostly matching the tags to be valid e.g. opening tag name must have a valid closing tag name.

I've inserted the xml into a table (but this is not mandatory) see below

DECLARE @tbl TABLE (XmlCol xml)
INSERT INTO @tbl VALUES 
('<option>
            <OptionName>Option 1</OptionName>
            <grant>
                    <GrantName>Grant 1</GrantName>
                            <schedules>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>1/1/2018</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>2/1/2018</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>3/1/2018</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                            </schedules>
            </grant>
            <grant>
                            <GrantName>Grant 2</GrantName>
                            <schedules>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>1/1/2019</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>2/1/2019</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                                            <schedule>
                                                            <scheduleID></scheduleID>
                                                            <scheduleName></scheduleName>
                                                            <scheduleDate>3/1/2019</scheduleDate>
                                                            <scheduleAmount></scheduleAmount>
                                            </schedule>
                            </schedules>
            </grant>
        </option>'
)



SELECT e.XmlCol.value('(/option/OptionName)[1]', 'varchar(100)'),
        t.x.value('../.././GrantName[1]','varchar(100)') GrantName,
       t.x.value('(./scheduleDate)[1]', 'varchar(100)') scheduleDate
 FROM @tbl e
    cross apply e.XmlCol.nodes ('/option/grant/schedules/schedule') t(x)