xml_in_sql.sql

--SETUP
DECLARE @test_xml_table TABLE (test_xml_column xml);


DECLARE @test_xml_local_variable xml;
SET @test_xml_local_variable = '<?xml version="1.0" encoding="utf-8"?>
<Countries>
  <Country >
    <CountryName>US</CountryName>
    <IncomeStatements>
      <IncomeStatement>
        <Company>South Co.</Company>
        <Revenue>5</Revenue>
        <Cost>
          3
        </Cost>
        <Year>2013</Year>
      </IncomeStatement>
      <IncomeStatement>
        <Company>South Co.</Company>
        <Revenue>10</Revenue>
        <Cost>8</Cost>
        <Profit>2</Profit>
        <Year>2014</Year>
      </IncomeStatement>
    </IncomeStatements>
  </Country>
  <Country>
    <CountryName>China</CountryName>
    <IncomeStatements>
      <IncomeStatement>
        <Company>East Co.</Company>
        <Revenue>5</Revenue>
        <Cost>3</Cost>
        <Profit>2</Profit>
        <Year>2013</Year>
        <WeirdThingsThatHappenedThisYear>
          <WeirdThing>China devaluing its currency</WeirdThing>
          <WeirdThing>Tianjin chemical gas explosion</WeirdThing>
        </WeirdThingsThatHappenedThisYear>
      </IncomeStatement>
     </IncomeStatements>
  </Country>
</Countries>';

INSERT INTO @test_xml_table VALUES (@test_xml_local_variable);


--Three important functions

--1) nodes()

--SYNTAX: {xml_datatype}.nodes('{xpath_string}') as xpath_result_table(xpath_result_column)
--Yes this suggests the qualified name of the column ix xpath_reult_table.xpath_result_column

--From a local variable of type xml:
SELECT country.query('.') FROM @test_xml_local_variable.nodes('Countries/Country') as countries(country);

--Same as above except explict column name:
--        v
SELECT countries.country.query('.') FROM @test_xml_local_variable.nodes('Countries/Country') as countries(country);

--From a column of type xml
--https://msdn.microsoft.com/en-us/library/ms188282.aspx#CodeSnippetContainerCode_03f821ac-af25-48a1-acdd-fa40499a579d
--Uses CROSS APPLY as described here
--https://technet.microsoft.com/en-US/library/ms175156(v=SQL.105).aspx
--													v			v			
SELECT countries.country.query('.') FROM @test_xml_table  CROSS APPLY test_xml_column.nodes('Countries/Country') as countries(country);

--Same as above except aliased table:
--													        v			  v			
SELECT countries.country.query('.') FROM @test_xml_table as t CROSS APPLY t.test_xml_column.nodes('Countries/Country') as countries(country);


SELECT '---------' as sep;

--2) query
--Used above, but "not explained"
--Returns the XML "fragment" at that point
--Returns the entire node, starting at its point
SELECT @test_xml_local_variable.query('.');

--Its point should be root:
SELECT @test_xml_local_variable.query('/');

--Testing a nonexistant node:
--Returns what, null empty string?
SELECT @test_xml_local_variable.query('/Nonexistant') ;

--Looks like empty string, verifying:
IF (SELECT @test_xml_local_variable.query('/Nonexistant')) IS NULL 
--Msg 305, Level 16, State 1, Line 91 The XML data type cannot be compared or sorted, except when using the IS NULL operator.
--= CAST('' as xml)
BEGIN
SELECT 'Is empty string';
END;
--Definitely not null, 
--And cannot compare it to (anything), much less an empty string else get the above error
--So not sure what the value is!


--Remember not 0-indexed, this doesn't return anything:
--												  v          v
SELECT @test_xml_local_variable.query('/Countries[0]/Country[0]/CountryName') ;

--It is 1-indexed, this does return
--												  v          v
SELECT @test_xml_local_variable.query('/Countries[1]/Country[1]/CountryName') ;

--Remember text() are nodes, too
--																			  v          
SELECT @test_xml_local_variable.query('/Countries[1]/Country[1]/CountryName/text()') ;


--And remember that if you don't specify an index, you can get unexpected results (i.e. 'none at all')
--															 v          
SELECT @test_xml_local_variable.query('/Countries[1]/Country/text()') ;


--3) value

--So you can "get out" of a datatype
--Can skip the /text(), it is implied... (i.e. take the children...)
SELECT @test_xml_local_variable.value('/Countries[1]/Country[1]/CountryName[1]', 'varchar(20)');
--US


--This will only work even if the children nodes are not plain text(); i.e. more than one children?
--But results are not what you expect:
--															v
SELECT @test_xml_local_variable.value('/Countries[1]/Country[1]', 'varchar(20)');
--USSouth Co.5

--4) modify
--Below will modify a value that was ".value-queried" above, then ".value-query" it to verify it changed
--A) modify starts with SET, is that necessary?
--yes, though it seems extraneous

--B)And is this extra [1] needed after the XPath?
-- Yes it is, or else:  XQuery [modify()]: The target of 'replace' must be at most one node, found 'text *'

--C)But ironically the precursor 1's (i.e. for CountryName, as there is only one per Country, or Countries, as it is the root and there is only one root) aren't necessary

--  Av			 Cv						   Cv		  Bv
	SET @test_xml_local_variable.modify(
	 'replace value of
	  (/Countries[1]/Country[1]/CountryName[1]/text())[1]
	  with "Changed Country Name" ');
	  
SELECT @test_xml_local_variable.value('/Countries[1]/Country[1]/CountryName[1]', 'varchar(20)');


--The reason for the ultimate index is to make sure you only change what is meant to change
SET @test_xml_local_variable.modify(
	
	 --  						Cv     Cv		Cv		
	 'replace value of  (/Countries/Country/CountryName/text())[1]
	  with "Again Changed Country Name " ');
	
--So this shows "Again Changed Country Name"
SELECT @test_xml_local_variable.value('/Countries[1]/Country[1]/CountryName[1]', 'varchar(20)');
--But this still shows "China"
SELECT @test_xml_local_variable.value('/Countries[1]/Country[2]/CountryName[1]', 'varchar(20)');

--5)Casting between types
--Inspired by CAST('' as xml)
--https://www.simple-talk.com/sql/learn-sql-server/sql-server-xml-questions-you-were-too-shy-to-ask/

SELECT CAST('' as xml)



--6) We can go from Relational to XML
--Either "flat" <row ColName1="ColValue1" ColName2="ColValue2">
--A) FOR XML RAW

--Or "nested" if JOINS are used
--<OuterTable OuterTableColName1="ColValue1">
--	<InnerJoinedTable InnerJoinedTableColName1="InnerJoinedTableColName1" />
--</OuterTable>
--B) FOR XML AUTO