Query to shred XML without CROSS APPLY

Question

I have the following XML:

DECLARE @Temp XML = 
'<SurveyQLADetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <FirstName>Jacal</FirstName>
    <LastName>Duardo</LastName>
    <PhoneNumber>7868256998</PhoneNumber>
    <Email>Duardo</Email>
    <SFId>00Q6R00001XWHWHUA5</SFId>
    <ClubNumber>7446</ClubNumber>
    <QLAList>
        <QLADetailsModel>
            <QuestionId>1</QuestionId>
            <OptionId>2</OptionId>
            <AnswerName xsi:type="xsd:string">Gain strength</AnswerName>
            <QuestionsTypeName>Fitness_Goals</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>2</QuestionId>
            <OptionId xsi:nil="true" />
            <AnswerName xsi:type="xsd:boolean">true</AnswerName>
            <QuestionsTypeName>Achieve_Fitness_Goals</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>3</QuestionId>
            <OptionId>9</OptionId>
            <AnswerName xsi:type="xsd:string">3-6 months</AnswerName>
            <QuestionsTypeName>Timeline_achieve_goals</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>4</QuestionId>
            <OptionId xsi:nil="true" />
            <AnswerName xsi:type="xsd:boolean">false</AnswerName>
            <QuestionsTypeName>Personal_Trainer</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>5</QuestionId>
            <OptionId>15</OptionId>
            <AnswerName xsi:type="xsd:string">Health reasons (doctor recommendation)</AnswerName>
            <QuestionsTypeName>Motivation_achieving_goals</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>6</QuestionId>
            <OptionId>19</OptionId>
            <AnswerName xsi:type="xsd:string">3</AnswerName>
            <QuestionsTypeName>Importance_Goals</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>7</QuestionId>
            <OptionId>29</OptionId>
            <AnswerName xsi:type="xsd:string">6 months or more</AnswerName>
            <QuestionsTypeName>Length__thought_goals</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>8</QuestionId>
            <OptionId xsi:nil="true" />
            <AnswerName xsi:type="xsd:boolean">false</AnswerName>
            <QuestionsTypeName>Anything_stopping_you_from_starting</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>8.1</QuestionId>
            <OptionId xsi:nil="true" />
            <AnswerName xsi:type="xsd:boolean">false</AnswerName>
            <QuestionsTypeName>Support_friends_family</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>9</QuestionId>
            <OptionId>38</OptionId>
            <AnswerName xsi:type="xsd:string">3+ per week</AnswerName>
            <QuestionsTypeName>Gym_Visits</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>10</QuestionId>
            <OptionId>42</OptionId>
            <AnswerName xsi:type="xsd:string">Evening</AnswerName>
            <QuestionsTypeName>What_time_of_the_day</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>11</QuestionId>
            <OptionId xsi:nil="true" />
            <AnswerName xsi:type="xsd:boolean">true</AnswerName>
            <QuestionsTypeName>Visit_other_locations</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>12</QuestionId>
            <OptionId>46</OptionId>
            <AnswerName xsi:type="xsd:string">HIIT or small group training</AnswerName>
            <QuestionsTypeName>Amenities_Important</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>13</QuestionId>
            <OptionId xsi:nil="true" />
            <AnswerName xsi:type="xsd:boolean">true</AnswerName>
            <QuestionsTypeName>Nutrition_Plan</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>14</QuestionId>
            <OptionId xsi:nil="true" />
            <AnswerName xsi:type="xsd:boolean">false</AnswerName>
            <QuestionsTypeName>Meal_Plan</QuestionsTypeName>
        </QLADetailsModel>
        <QLADetailsModel>
            <QuestionId>15</QuestionId>
            <OptionId xsi:nil="true" />
            <OtherAnswerName>
                <OtherAnswerDetails>
                    <FriendsName>t</FriendsName>
                    <FriendsPhoneNo>t</FriendsPhoneNo>
                </OtherAnswerDetails>
                <OtherAnswerDetails>
                    <FriendsName>t</FriendsName>
                    <FriendsPhoneNo>t</FriendsPhoneNo>
                </OtherAnswerDetails>
            </OtherAnswerName>
        </QLADetailsModel>
    </QLAList>
</SurveyQLADetails>'

I want output as below without using CROSS APPLY on SQL Server.

Enter image description here

I have tried below query it shows the same output but I don't want to use CROSS APPLY:

SELECT s.l.value('(FirstName/text())[1]', 'VARCHAR(250)') AS FirstName,
    s.l.value('(LastName/text())[1]', 'VARCHAR(250)') AS LastName,
    s.l.value('(PhoneNumber/text())[1]', 'VARCHAR(250)') AS PhoneNumber,
    s.l.value('(Email/text())[1]', 'VARCHAR(250)') AS Email,
    s.l.value('(SFId/text())[1]', 'VARCHAR(250)') AS SFId,
    s.l.value('(ClubNumber/text())[1]', 'VARCHAR(250)') AS ClubNumber,
    QLAList.QLADetailsModel.value('(./QuestionId)[1]', 'VARCHAR(MAX)') AS QuestionId,
    QLAList.QLADetailsModel.value('(./OptionId)[1]', 'VARCHAR(MAX)') AS OptionId,
    QLAList.QLADetailsModel.value('(./AnswerName)[1]', 'VARCHAR(MAX)') AS AnswerName,
    QLAList.QLADetailsModel.value('(./OtherAnswerName/OtherAnswerDetails/FriendsName)[1]', 'VARCHAR(MAX)') AS FriendsName,
    QLAList.QLADetailsModel.value('(./OtherAnswerName/OtherAnswerDetails/FriendsPhoneNo)[1]', 'VARCHAR(MAX)') AS FriendsPhoneNo,
    QLAList.QLADetailsModel.value('(./QuestionsTypeName)[1]', 'VARCHAR(MAX)') AS QuestionsTypeName
FROM @Temp.nodes('(/SurveyQLADetails/QLAList/QLADetailsModel)') AS QLAList(QLADetailsModel)
CROSS APPLY @Temp.nodes('/SurveyQLADetails') s(l)

Output that I want without using CROSS APPLY

Is there any query that does not use WHERE and does not use CROSS APPLY?

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jun 09 '23 at 07:25
You can use openxml to do this. I answered similar question a while ago to another guy who had cross apply allergy : https://stackoverflow.com/questions/74428144/how-can-i-use-with-inside-apply-in-sql-server-i-know-another-solution-without-a/74434561#74434561 — siggemannen, Jun 09 '23 at 07:48
Yes, you _could_ use `OPENXML` for this, but don't. It's not set-based and leaks resources when you forget to invoke `sp_xml_removedocument` (as 90% of the Microsoft examples tend to do). — AlwaysLearning, Jun 09 '23 at 08:11
I don't see a difference between your two screen shots. The invocations of `@Temp.nodes()` as you have them are not correlated, they're independent, so is your problem with `CROSS APPLY` that it multiplies the result set when there are multiple `/SurveyQLADetails` elements? — AlwaysLearning, Jun 09 '23 at 08:15
there is no problem with CROSS APPLY but my ma'am said there's another way to get the same output without CROSS APPLY and she gave me a hint like you can use SELECT inside the SELECT .. like SELECT COL1 , COL2 , SELECT() .. FROM ... i tried but don't find an answer — Janki Gabani, Jun 09 '23 at 09:33

score 0 · Answer 1 · answered Jun 09 '23 at 11:28

You can do it as follows

SELECT 
    QLAList.QLADetailsModel.value('(/SurveyQLADetails/FirstName/text())[1]', 'VARCHAR(250)') AS FirstName,
    QLAList.QLADetailsModel.value('(/SurveyQLADetails/LastName/text())[1]', 'VARCHAR(250)') AS LastName,
    QLAList.QLADetailsModel.value('(/SurveyQLADetails/PhoneNumber/text())[1]', 'VARCHAR(250)') AS PhoneNumber,
    QLAList.QLADetailsModel.value('(/SurveyQLADetails/Email/text())[1]', 'VARCHAR(250)') AS Email,
    QLAList.QLADetailsModel.value('(/SurveyQLADetails/SFId/text())[1]', 'VARCHAR(250)') AS SFId,
    QLAList.QLADetailsModel.value('(/SurveyQLADetails/ClubNumber/text())[1]', 'VARCHAR(250)') AS ClubNumber,
    QLAList.QLADetailsModel.value('(./QuestionId)[1]', 'VARCHAR(MAX)') AS QuestionId,
    QLAList.QLADetailsModel.value('(./OptionId)[1]', 'VARCHAR(MAX)') AS OptionId,
    QLAList.QLADetailsModel.value('(./AnswerName)[1]', 'VARCHAR(MAX)') AS AnswerName,
    QLAList.QLADetailsModel.value('(./OtherAnswerName/OtherAnswerDetails/FriendsName)[1]', 'VARCHAR(MAX)') AS FriendsName,
    QLAList.QLADetailsModel.value('(./OtherAnswerName/OtherAnswerDetails/FriendsPhoneNo)[1]', 'VARCHAR(MAX)') AS FriendsPhoneNo,
    QLAList.QLADetailsModel.value('(./QuestionsTypeName)[1]', 'VARCHAR(MAX)') AS QuestionsTypeName
FROM @Temp.nodes('(/SurveyQLADetails/QLAList/QLADetailsModel)') AS QLAList(QLADetailsModel)

I'd probably be more minded to do it as below though so at least the CROSS APPLY is building on the previous nodes result. This lack of any correlation may well be why your colleague is not keen on your current usage.

SELECT 
    SurveyQLADetails.SurveyQLADetailsModel.value('(./FirstName/text())[1]', 'VARCHAR(250)') AS FirstName,
    SurveyQLADetails.SurveyQLADetailsModel.value('(./LastName/text())[1]', 'VARCHAR(250)') AS LastName,
    SurveyQLADetails.SurveyQLADetailsModel.value('(./PhoneNumber/text())[1]', 'VARCHAR(250)') AS PhoneNumber,
    SurveyQLADetails.SurveyQLADetailsModel.value('(./Email/text())[1]', 'VARCHAR(250)') AS Email,
    SurveyQLADetails.SurveyQLADetailsModel.value('(./SFId/text())[1]', 'VARCHAR(250)') AS SFId,
    SurveyQLADetails.SurveyQLADetailsModel.value('(./ClubNumber/text())[1]', 'VARCHAR(250)') AS ClubNumber,
    QLAList.QLADetailsModel.value('(./QuestionId)[1]', 'VARCHAR(MAX)') AS QuestionId,
    QLAList.QLADetailsModel.value('(./OptionId)[1]', 'VARCHAR(MAX)') AS OptionId,
    QLAList.QLADetailsModel.value('(./AnswerName)[1]', 'VARCHAR(MAX)') AS AnswerName,
    QLAList.QLADetailsModel.value('(./OtherAnswerName/OtherAnswerDetails/FriendsName)[1]', 'VARCHAR(MAX)') AS FriendsName,
    QLAList.QLADetailsModel.value('(./OtherAnswerName/OtherAnswerDetails/FriendsPhoneNo)[1]', 'VARCHAR(MAX)') AS FriendsPhoneNo,
    QLAList.QLADetailsModel.value('(./QuestionsTypeName)[1]', 'VARCHAR(MAX)') AS QuestionsTypeName
FROM @Temp.nodes('(/SurveyQLADetails)') AS SurveyQLADetails(SurveyQLADetailsModel)
CROSS APPLY SurveyQLADetailsModel.nodes('QLAList/QLADetailsModel') QLAList(QLADetailsModel)

Query to shred XML without CROSS APPLY

1 Answers1