notes about xml processing in sql server

----working with xml data
--retrieving relational data as xml


--passing data a xml

--storing and querying actual
use AdventureWorks;

select
c.CustomerID,
c.AccountNumber,
c.CustomerType

from sales.Customer as c
where c.CustomerID in (1,2) ;

select soh.CustomerID,
soh.SalesOrderID,
soh.OrderDate,
soh.ShipDate
from Sales.SalesOrderHeader as soh
where soh.CustomerID in (1,2);

--instead use join query
select c.customerid , c.accountnumber, c.customertype,
soh.SalesPersonID, soh.OrderDate, soh.ShipDate

from
Sales.Customer as c left outer join

Sales.SalesOrderHeader as soh on soh.CustomerID = c.CustomerID
where c.CustomerID in (1,2)

--xml
select c.customerid as "@Id"
, c.accountnumber as "@AccountNumber"
, c.customertype as "@Type"
,(
SELECT
soh.SalesOrderID as "@Id"
,soh.OrderDate as "@OrderDate"
, soh.ShipDate as "@ShipDate"
 from Sales.SalesOrderHeader as soh
 where soh.CustomerID = c.CustomerID
 for XML Path('Order') , TYPE
) as "Orders"
from
Sales.Customer as c  
where c.CustomerID in (1,2)
FOR XML PATH('Customer'), ROOT('Customers');

---
USe AdventureWorks;
go

select
p.ProductID as "@Id",
p.Name as "Info/@Name",
p.ListPrice as "Info/@Listprice",
(
select
sod.SalesOrderID as "@Orderid",
sod.OrderQty as "@QTY",
soh.OrderDate as "@date",
soh.CustomerID as "@customerid"

from Sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail as sod
on sod.SalesOrderID = soh.SalesOrderID
where sod.ProductID = p.ProductID
for XML path('Ordersub'), TYPE
)
from Production.Product as p
where p.ProductID = 707
for xml path('Product');

---master order and details
select
p.ProductID as "@Id",
p.Name as "Info/@Name",
p.ListPrice as "Info/@Listprice",
(
select
sod.SalesOrderID as "@Orderid",
sod.OrderQty as "@QTY",
soh.OrderDate as "@date",
soh.CustomerID as "@customerid"

from Sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail as sod
on sod.SalesOrderID = soh.SalesOrderID
where sod.ProductID = p.ProductID
for XML path('Ordersub'), TYPE
)
from Sales.SalesOrderHeader soh
where p.ProductID = 707
for xml path('Product');

--list top 10 orders with amount max

--
select  top 1
sh.SalesOrderID as "@ID",
sh.OrderDate as "@OrderDate",
sh.ShipDate as "@ShipDate",
sh.Status as "@Status",
sh.CustomerID as "@CustomerID",
sh.SalesPersonID as "@SalesPersonID",
sh.Totaldue as "@TotalDue",
(select top 5
sod.ProductID,
sod.OrderQty,
sod.LineTotal
from
 Sales.SalesOrderDetail sod
 where sod.SalesOrderID = sh.SalesOrderID
 order by sod.LineTotal desc
for XML path('Ordersub'), TYPE
 )
 from Sales.SalesOrderHeader sh
 order by sh.TotalDue desc
for xml path('Sales') ,root('SalesTop10')

;


--read test
Declare @myxml XML

set @myxml = N'

  
    
      969
      21
      26159.208075
  
    
      966
      16
      19930.825200
  
    
      954
      16
      12206.438400
  
    
      892
      23
      12064.488425
  
    
      967
      6
      8582.652000
  
 
';

select @myxml.query('

{for $o in /SalesTop10/Sales/Ordersub
 where  $o/OrderQty  > 16
 return  
  }

')

/* output

  
  

*/


---all of them in properties
select  top 1
sh.SalesOrderID as "@ID",
sh.OrderDate as "@OrderDate",
sh.ShipDate as "@ShipDate",
sh.Status as "@Status",
sh.CustomerID as "@CustomerID",
sh.SalesPersonID as "@SalesPersonID",
sh.Totaldue as "@TotalDue",
(select top 5
sod.ProductID as "@ProductID",
sod.OrderQty as "@QTY",
sod.LineTotal as "LineTotal"
from
 Sales.SalesOrderDetail sod
 where sod.SalesOrderID = sh.SalesOrderID
 order by sod.LineTotal desc
for XML path('Ordersub'), TYPE
 )
 from Sales.SalesOrderHeader sh
 order by sh.TotalDue desc
for xml path('Sales') ,root('SalesTop1')

;

declare @otherxml XML
set @otherxml = N'

  
    
      26159.208075
  
    
      19930.825200
  
    
      12206.438400
  
    
      12064.488425
  
    
      8582.652000
  
 
';


---reshape results with xquery

SELECT (SELECT top 10 * FROM Sales.Customer as Customers FOR XML AUTO,type   ).query(
'{
   for $c in /Customers
   return
    
 }')

 ---
 ;
 create schema TestXml;
 go

 create table TestXml.Messages
 (
 MessageID int identity primary key,
 FromUser nvarchar(50) not null,
 Message nvarchar(max) not null,
 CreatedDateTime datetime2 not null default sysdatetime()
 );

 --proc to insert data from xml input

 create procedure TestXml.spMessageAddMany
 @Message XML
 as
 Begin
set nocount on;
Insert TestXml.Messages (FromUser, Message)
 Select
  tab.col.value('@FromUser', 'NVARCHAR(50)'),
  tab.col.value('text()[1]', 'NVARCHAR(max)')
 from @Message.nodes('/Messages/Message') as tab(col);
end

---now insert some xml messages


EXEC TestXml.spMessageAddMany @Message = N'

Naber nasil?
Naber nasil2?
Naber nasil3?
';

Select * from TestXml.Messages;

--working with xml stored in xml variable or column
EXIST METHOd

select top 1 Demographics
from Sales.Individual

with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select COUNT(*)
from Sales.Individual
where Demographics.exist(
 '/IndividualSurvey/Occupation') = 1

with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select COUNT(*)
from Sales.Individual
where Demographics.exist(
 '/IndividualSurvey/TotalPurchaseYTD[xs:decimal(.) > 8000]') = 1


with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select COUNT(*)
from Sales.Individual
where Demographics.exist(
 '/IndividualSurvey/TotalPurchaseYTD[xs:decimal(.) > 8000]') = 1

 ---Extract Properties from XML Variable
DECLARE @xVar XML
SET @xVar =
'


  Michael
  Howard


  David
  LeBlanc
  
 2David
 2LeBlanc
 3LeBlanc
 

39.99
'

SELECT nref.value('first-name[1]', 'nvarchar(50)') FirstName, --get first element [1]
   nref.value('last-name[1]', 'nvarchar(50)') LastName
FROM   @xVar.nodes('//author') AS R(nref)
WHERE  nref.exist('.[first-name != "David"]') = 1

--value method

with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select SUM(demographics.value('count(/IndividualSurvey)', 'INT')) AS NumberofISurveys
from Sales.Individual
 --TotalPurchaseYTD
 with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select SUM(demographics.value('sum(/IndividualSurvey/TotalPurchaseYTD)', 'decimal(10,2)')) AS NumberofISurveys
from Sales.Individual

--avg TotalChildren
 with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select  (demographics.value('sum(/IndividualSurvey/TotalChildren)', 'decimal(10,2)')) AS NumberofISurveys
from Sales.Individual

---NODES

declare @orders xml;
set @orders = N'

a
b
c
';
 SELECT nref.value('Order[1]', 'nvarchar(50)') rowOrder  --get first element [1]
 ,nref.value('Product[1]', 'nvarchar(50)') rowOrderProduct
FROM   @orders.nodes('//Orders') AS R(nref)


 DECLARE @T varchar(max)
SET @T =
'
    
        
                
                
      
        
                
      
  
    
        
                
                
      
        
                
      
  
'

DECLARE @X xml

SET @X = CAST(@T as xml)
SELECT
   YY.t.value('(@FieldRowId)[1]', 'int') as FieldID,
   YY.t.value('(Items/Item/@Name)[1]', 'varchar(max)') as "Name",
   YY.t.value('(Attributes/Attribute/@ID)[1]', 'int') as AttributeID
FROM @X.nodes('/root/Field') as YY(t)