How to retrieve multiple XML siblings - SQL Server

Question

I have searched thoroughly and not found any examples or guidance on how I can query and retrieve the values of multiple XML siblings.

There are plenty of examples on how to retrieve a single sibling value.

For example given the following XML fragment:

<StoreSurvey>
  <AnnualSales>800000</AnnualSales>
  <AnnualRevenue>80000</AnnualRevenue>
  <BankName>United Security</BankName>
  <BusinessType>BM</BusinessType>
  <YearOpened>1996</YearOpened>
  <Specialty>Mountain</Specialty>
  <SquareFeet>21000</SquareFeet>
  <Brands>2</Brands>
  <Internet>ISDN</Internet>
  <NumberEmployees>13</NumberEmployees>
  <Products Type="Bikes">
    <Product>Mountain</Product>
    <Product>Road</Product>
    <Product>Racing</Product>
  </Products>
  <Products Type="Clothes">
    <Product>Jerseys</Product>
    <Product>Jackets</Product>
    <Product>Shorts</Product>
  </Products>
</StoreSurvey>

If I want to retrieve the value of <AnnualSales>, I can execute the following statement in query analyzer:

SELECT Survey_untyped.query('/StoreSurvey/AnnualSales') 
FROM Stores;

The result set will properly display

<AnnualSales>800000</AnnualSales>

However, what if I want to retrieve both <AnnualSales> AND <AnnualRevenue>? How would I do that?

The query should provide a result set looking like:

<AnnualSales>800000</AnnualSales>
<AnnualRevenue>80000</AnnualRevenue>

Or what if I want three of the sibling values “BankName” in addition to those values? Result set would look like this:

  <AnnualSales>800000</AnnualSales>
  <AnnualRevenue>80000</AnnualRevenue>
  <BankName>United Security</BankName>

Does anyone know the answer to that?

kjhughes · Accepted Answer · 2017-02-23 17:25:38Z

2

This XPath, which uses the self:: axis,

/StoreSurvey/*[self::AnnualSales or self::AnnualRevenue or self::BankName]

will select

<AnnualSales>800000</AnnualSales>
<AnnualRevenue>80000</AnnualRevenue>
<BankName>United Security</BankName>

from your XML, as requested.

edited Feb 23, 2017 at 17:25

answered Feb 23, 2017 at 3:32

kjhughes

113k31 gold badges198 silver badges276 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SharmaVenkati Over a year ago

Thank you, I have not seen any references that use that use that syntax "self" etc. Could you please post a link to that? (I have been looking at standard x-query examples, etc.)

SharmaVenkati Over a year ago

actually, now that i look I see "self" in there, but it was described as selecting "the current node" Never saw any examples like your's but thank you very much, you saved me a lot of time. Thanks again.

kjhughes Over a year ago

@SharmaVenkati: Answer updated with self:: axis link per your request.

kjhughes Over a year ago

You're welcome. Please accept this answer if it's helped. Thanks.

Collectives™ on Stack Overflow

How to retrieve multiple XML siblings - SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related