--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'
';
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'
';
---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'
';
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 =
'
'
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'
';
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)
