HoneyHoney

use adventureworks2008r2

--IF OBJECT_ID('XML_TEST') IS NOT NULL
--    DROP TABLE XML_TEST

--GO

--CREATE TABLE XML_TEST (
--    ID INT IDENTITY(1, 1),
--    NAME VARCHAR(20),
--    DATA XML,
--CONSTRAINT PK_XML_TEST PRIMARY KEY CLUSTERED (ID)
--)


--INSERT INTO XML_TEST (NAME, DATA)
--VALUES
--('ZhOnG', '<storesurvey>
--    <annualsales>700000</annualsales>
--    <annualrevenue>70000</annualrevenue>
--    <bankname>United Phoenix</bankname>
--    <businesstype>BM</businesstype>
--    <yearopened>2016</yearopened>
--    <specialty>Mountain</specialty>
--    <squarefeet>21000</squarefeet>
--    <brands>2</brands>
--    <internet>ISDN</internet>
--    <numberemployees>13</numberemployees>
--    <products type="Swimming">
--      <product>Mountain</product>
--      <product>Road</product>
--      <product>Racing</product>
--    </products>
--    <products type="Endurance">
--      <product>Jerseys</product>
--      <product>Jackets</product>
--      <product>Shorts</product>
--    </products>
--  </storesurvey>')


SELECT *,
    data.query('/StoreSurvey/AnnualRevenue'),
    data.value('(/StoreSurvey/AnnualRevenue)[1]', 'INT'),
    data.value('(/StoreSurvey/Products/@Type)[2]', 'varchar(10)'),

    data.query('/StoreSurvey/child::node()'),
    data.exist('/StoreSurvey[BusinessType="BM"]'),
    data.query('/StoreSurvey/Products'),
    data.query('/StoreSurvey/Products[@Type="Bikes"]')
FROM XML_TEST


SELECT Bike.Product.query('./text()') AS BikeTypes,
        Bike.Product.value('local-name(.)[1]', 'varchar(100)')
FROM XML_TEST
CROSS APPLY DATA.nodes('/StoreSurvey/Products[@Type="Bikes"]/Product') AS Bike(Product);


DECLARE @bikes XML
SET @bikes =
  '<products>
    <product>Mountain</product>
    <product>Road</product>
    <product>Racing</product>
   </products>'

SELECT
  --Category, -- you could not use this directly
  Category.query('./text()') AS BikeTypes
FROM
  @bikes.nodes('/Products/Product') AS Bike(Category);