Query XML file in MS SQL Server

1. create a table with XML field

CREATE TABLE Stores

(

  StoreID INT PRIMARY KEY,

  Survey_untyped XML

);

 INSERT INTO Stores

 VALUES

 (

   292,

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

);

2. If you want to return a specific element and its child elements, you can do so by referencing one of its attributes.

SELECT

  Survey_untyped.query('/StoreSurvey/Products[@Type="Bikes"]')

    AS BikeProducts

FROM

  Stores;

Reference:

1. https://www.simple-talk.com/sql/learn-sql-server/the-xml-methods-in-sql-server/