February 1, 2018 at 4:59 am
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.
February 1, 2018 at 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 2, 2018 at 2:03 am
drew.allen - Thursday, February 1, 2018 8:34 AMIf 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.
February 2, 2018 at 12:33 pm
gzt4gs - Friday, February 2, 2018 2:03 AMdrew.allen - Thursday, February 1, 2018 8:34 AMIf 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 1There 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 OwnershipI 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy