Selecting item from xml where multiples can exist.

Question

Selecting item from xml where multiples can exist.

gzt4gs

Valued Member

Points: 69
More actions
February 1, 2018 at 4:59 am

#104655

I have some xml which looks like this:
<cBusinessAppraisal FilePath="_Training_NPV TEST\PROVAL TEST FB" CurrentUser="Alvin Jones" Database="SDSProValWorking" Version="015003028" MinimumClientVersion="015003020">
   <ProductsSummaryItems>
    <CollectionItems>
      <cUnitSummaryData>
        <CollectionItems>
        </CollectionItems>
        <Children>
          <CollectionItems>
            <cUnitSummaryData>
              <CollectionItems>
                <cSummaryBaseItem>
                  <Display Value="Total Units" />
                  <Format Value="#,0" />
                  <Value Value="1" />
                </cSummaryBaseItem>
                <cSummaryBaseItem>
                  <Display Value="Total Persons" />
                  <Format Value="#,0" />
                  <Value Value="4" />
                </cSummaryBaseItem>
                <cSummaryBaseItem>
                  <Display Value="NPV at First Handover" />
                  <Format Value="c0" />
                  <Value Value="-14780.277243585915456955456701" />
                </cSummaryBaseItem>
                <cSummaryBaseItem>
                  <Display Value="IRR" />
                  <Format Value="#,0.00 %" />
                  <Value Value="0.0519488817079652538226875664" />
                </cSummaryBaseItem>
                <cSummaryBaseItem>
                  <Display Value="TSC / MSV" />
                  <Format Value="#,0.00 %" />
                  <Value Value="0.8879259177778695720875398512" />
                </cSummaryBaseItem>
                <cSummaryBaseItem>
                  <Display Value="Loan Repaid Year" />
                  <Format Value="#,0" />
                  <Value Value="50" />
                </cSummaryBaseItem>
              </CollectionItems>
              <Persons Value="4" />
              <HabRooms Value="3" />
    <Children>
                <CollectionItems />
              </Children>
              <GrossArea Value="74.99" />
              <Persons Value="4" />
              <SummaryUnits Value="1" />
              <Title Value="A: 2B4P House" />
              <Units Value="1" />
            </cUnitSummaryData>
          </CollectionItems>
        </Children>
        <GrossArea Value="74.99" />
        <IsVisible Value="True" />
        <Persons Value="4" />
        <ProductTypeID Value="Affordable Rent" />
        <SummaryUnits Value="1" />
        <Title Value="Affordable Rent" />
        <Units Value="1" />
      </cUnitSummaryData>
    </CollectionItems>
</ProductsSummaryItems>
</cBusinessAppraisal>
Using Sql I need to get the value in the Title row, the value shown here is ="A: 2B4P House".
I can get the value using this SQL:
Select    Title = Title.c.value('@Value', 'nVarChar(max)')
fromAppraisals
outer apply aAppraisal.nodes('//Title') AS Title(c)
The issue here is that "Title" can appear multiple times in different paths but I want the value from this specific section. How do I set up the node so it will select the correct information.
I hope this makes sense, I am new to using XML in a Sql database.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

If you want a specific section, use the path that gives you that specific section.

SELECT Title = Title.c.value('@Value', 'nVarChar(max)') FROM Appraisals CROSS APPLY aAppraisal.nodes('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title') Title(c)

Also, does this really need to be NVARCHAR(MAX)?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

gzt4gs Valued Member Points: 69 More actions · Answer 2

drew.allen - Thursday, February 1, 2018 8:34 AM
If you want a specific section, use the path that gives you that specific section.
SELECT Title = Title.c.value('@Value', 'nVarChar(max)') FROM Appraisals CROSS APPLY aAppraisal.nodes('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title') Title(c)
Also, does this really need to be NVARCHAR(MAX)?
Drew

Hi Drew,
Thanks for the answer. Unfortunatley it does not solve my problem.
The <ProductsSummaryItems> section can be repeated multiple times with different values. This gives me multiple rows with mixed data. For example if I try to select the Titles and Units using this:

SELECT

SELECT Title Title == Title Title..cc..valuevalue(('@Value''@Value',, 'nVarChar(max)''nVarChar(max)'),),Units Units == Units Units..cc..valuevalue(('@Value''@Value',, 'nVarChar(max)''nVarChar(max)'),),

FROMFROM Appraisals AppraisalsCROSSCROSS APPLYAPPLY aAppraisal aAppraisal..nodesnodes(('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title''/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title')) Title Title((cc))CROSSCROSS APPLYAPPLY aAppraisal aAppraisal..nodesnodes(('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Units''/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Units')) Units Units((cc))

wherewhereaID aID == '9D07E6EA-C8E0-421F-A71F-197CDAECEC3C''9D07E6EA-C8E0-421F-A71F-197CDAECEC3C'

I get this result:
Title Units
A: 2B4P House 1
A: 2B4P House 1
B: 2B4P House 1
B: 2B4P House 1

There should be just one row for each Title.

Also I need to associate the Product Type with the Title to get something like this:
Title                   Units Product
A: 2B4P House 1         Affordable Rent
B: 2B4P House 1         Shared Ownership

I have tried by add another Cross Apply "

CROSS APPLY aAppraisal.nodes('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/ProductTypeID') Prod(c)"
But that then returns no rows.

drew.allen SSC Guru Points: 76985 More actions · Answer 3

gzt4gs - Friday, February 2, 2018 2:03 AM
drew.allen - Thursday, February 1, 2018 8:34 AM
If you want a specific section, use the path that gives you that specific section.
SELECT Title = Title.c.value('@Value', 'nVarChar(max)') FROM Appraisals CROSS APPLY aAppraisal.nodes('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title') Title(c)
Also, does this really need to be NVARCHAR(MAX)?
Drew
Hi Drew,
Thanks for the answer. Unfortunatley it does not solve my problem.
The <ProductsSummaryItems> section can be repeated multiple times with different values. This gives me multiple rows with mixed data. For example if I try to select the Titles and Units using this:
SELECT
SELECT Title Title == Title Title..cc..valuevalue(('@Value''@Value',, 'nVarChar(max)''nVarChar(max)'),),Units Units == Units Units..cc..valuevalue(('@Value''@Value',, 'nVarChar(max)''nVarChar(max)'),),
FROMFROM Appraisals AppraisalsCROSSCROSS APPLYAPPLY aAppraisal aAppraisal..nodesnodes(('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title''/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title')) Title Title((cc))CROSSCROSS APPLYAPPLY aAppraisal aAppraisal..nodesnodes(('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Units''/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Units')) Units Units((cc))
wherewhereaID aID == '9D07E6EA-C8E0-421F-A71F-197CDAECEC3C''9D07E6EA-C8E0-421F-A71F-197CDAECEC3C'
I get this result:
Title Units
A: 2B4P House 1
A: 2B4P House 1
B: 2B4P House 1
B: 2B4P House 1
There should be just one row for each Title.
Also I need to associate the Product Type with the Title to get something like this:
Title                   Units Product
A: 2B4P House 1         Affordable Rent
B: 2B4P House 1         Shared Ownership
I have tried by add another Cross Apply "
CROSS APPLY aAppraisal.nodes('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/ProductTypeID') Prod(c)"
But that then returns no rows.

The solution can only be as good as the quality of the data and information that you provide. If you want a better solution, I suggest that you provide better quality data to work with.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA