SQL Server XQuery always returning null

December 14, 2023

I’m learning how to read XML using SQL and currently struggling to return the desired data that I was expecting.

For the given example, I’m expecting to return "tagFourData" with the value of "Data". Instead I always seem to get NULL

declare @data xml = '<object>
    <Overview Requester="Me">
        <Request GetData="Y" Title="Get First SQL DataSet in XML" Id="1">
            <Detailed tagOne="yap" tagTwo="Yap">
                <!--Further elements/attributes if needed -->
            </Detailed>
        </Request>
        <Request GetData="N" Title="Get Second SQL DataSet in XML" Id="2">
            <Detailed tagThree="yap" tagFour="Yap">
                <!--Further elements/attributes if needed -->
            </Detailed>
        </Request>
        <Request GetData="Y" Title="Get Third SQL DataSet in XML" Id="3">
            <Detailed tagFour="Data" tagFive="data2" />
        </Request>
    </Overview>
</object>'

select @data.value('(object/Overview/Request/Detailed/tagFour/node())[1]', 'nvarchar(max)') as tagFourData

Is there something wrong with how I’ve written my Select statement?

Preferable also looking at a solution which can be dynamic. That is to say tagFour could be under a completely different Id but the structure Object/Overview/Request/Detailed would always be the same
Thank you

>Solution :

You were close:

select @data.value('(object/Overview/Request/Detailed/@tagFour)[1]', 'nvarchar(max)') as tagFourData

Need an extra @ to indicate attribute instead of child element.

But I’m not sure how you would generalize this further, as additional items inside the Detailed element are not children of the @tagFour for purposes of an XPath query. Once you select into an attribute, you have selected into the attribute, and children of that attribute’s element are no longer part of the same path.