Selecting item from xml where multiples can exist.

  • gzt4gs

    Valued Member

    Points: 59

    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)')

    from Appraisals

    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.

  • drew.allen

    SSC Guru

    Points: 76580

    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: 59

    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))

    wherewhere aID 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: 76580

    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))

    wherewhere aID 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

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

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