SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selecting item from xml where multiples can exist.


Selecting item from xml where multiples can exist.

Author
Message
gzt4gs
gzt4gs
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 14
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
drew.allen
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63932 Visits: 17003
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
gzt4gs
gzt4gs
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 14
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
drew.allen
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63932 Visits: 17003
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search