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)

That XML isn't valid. For example, the `option` tag is not closed, the `GrantName` tag is closed with `Name`, `scheduleID` is closed with `ID`, and many many more similar examples; this implies you're storing it as a `varchar`. You can't run XQuery against a `varchar`, and you can't convert a `varchar` to `xml` if it's not valid XML. You'll need to fix your data first before you can even start to consider parsing this in SQL Server. — Thom A, May 17 '18 at 10:16
Always store data in a datatype that is relevant to it and you won't have this problem. As you can't store invalid XML in the `xml` datatype, you would never have been able to store such poorly formed data. Store XML as `xml` dates as `date`, datetimes as `datetime2`/`datetimeoffset`, numbers as a numericdata type (i.e. `int`, `decimal`, **not** `money` or `float`, which are known to have inaccuracies). — Thom A, May 17 '18 at 10:22
Sorry for the poor XML. it's just a sample xml which i created for my reference. I do not have control on the table and table column. My requirement is to query the table and get the result in the format that i mentioned. — Richi Sharma, May 17 '18 at 10:29
The option closing tag is not displaying, may be they have a space constraints. also grant closing tag is miss spelled. — Richi Sharma, May 17 '18 at 10:31

score 4 · Accepted Answer · answered May 17 '18 at 10:44

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)

I'd, personally, suggest returning `scheduleDate` as a `date`, rather than `varchar`; it is a Date after all.. Other than that, +1. — Thom A, May 17 '18 at 10:46
It's a good point, however I'd do it with Cast/Convert (SQL) rather than define date in the xquery, just in case there is a corrupted data and you need to manage errors without failing the script — hkravitz, May 17 '18 at 10:50
@hkravitz thanks a tonn for correcting the XML and also the answer. u have saved me. — Richi Sharma, May 17 '18 at 11:00
How I select scheduleID,scheduleName,scheduleAmount columns here pls — Sreenu131, May 17 '18 at 11:14
Just take the sample I used here: t.x.value('(./scheduleDate)[1]', and change to the element name you wish to query, they are siblings to the Schedule Date element — hkravitz, May 17 '18 at 14:50

Get values from xml table column

1 Answers1