SQL XML

FOR XML AUTO	OPENROWSET
XML PATH	sp_xml_preparedocument
XML PATH('xyz'), ROOT('xuys')	sp_xml_removedocument
Attribute	sp_db_selective_xml_index
Sub Element
Select into variable

1) SELECT : - From RDBMS Table to Xml 

USE [WideWorldImporters]

--FOR XML AUTO
SELECT  Top 3
PersonID, FullName, LogonName,
IsSystemUser, IsEmployee, IsSalesperson,
PhoneNumber, FaxNumber, EmailAddress
FROM  Application.People
FOR XML AUTO -- it creates XML document without Parent tag

=================================================================
--FOR XML PATH
SELECT  Top 3
PersonID, FullName, LogonName,
IsSystemUser, IsEmployee, IsSalesperson,
PhoneNumber, FaxNumber, EmailAddress
FROM  Application.People
FOR XML PATH('Person') -- this will create each record under head 'People'
=================================================================
--ROOT
SELECT  Top 3
PersonID, FullName, LogonName,
IsSystemUser, IsEmployee, IsSalesperson,
PhoneNumber, FaxNumber, EmailAddress
FROM  Application.People
FOR XML PATH('Person'),
ROOT('People') -- this will create each record under head 'People'

=================================================================
-- Adding attributes
SELECT  Top 3
PersonID AS [@PersonID], FullName, LogonName,
IsSystemUser AS [UserType/@IsSystemUser], --@attributes
IsEmployee AS [UserType/@IsEmployee],
IsSalesperson AS [UserType/@IsSalesperson],

PhoneNumber, FaxNumber, EmailAddress
FROM  Application.People
FOR XML PATH('Person'),
ROOT('People')

=================================================================
-- Adding subelement
SELECT  Top 3
PersonID AS [@PersonID], FullName, LogonName,
IsSystemUser AS [UserType/IsSystemUser], --@attributes
IsEmployee AS [UserType/IsEmployee],
IsSalesperson AS [UserType/IsSalesperson],
PhoneNumber AS [Contact/PhoneNumber],
FaxNumber AS [Contact/FaxNumber],
EmailAddress  AS [Contact/EmailAddress]
FROM  Application.People
FOR XML PATH('Person'),
ROOT('People')
=================================================================
>> SELECT INTO VARIABLE
-- variable
DECLARE @x xml
SET @x =
(SELECT  Top 3
PersonID, FullName, LogonName,
IsSystemUser, IsEmployee, IsSalesperson,
PhoneNumber, FaxNumber, EmailAddress
FROM  Application.People
FOR XML PATH('Person'),
ROOT('People'), TYPE) --- without TYPE also it is working
=================================================================
>> select from two tables
SELECT SupplierName, PrimaryContactPersonId,
(SELECT
PersonID, FullName, LogonName,
IsSystemUser, IsEmployee, IsSalesperson,
PhoneNumber, FaxNumber, EmailAddress
FROM  Application.People P
WHERE P.PersonID = S.PrimaryContactPersonID
FOR XML PATH('Person'), ROOT('People'), TYPE)
FROM Purchasing.Suppliers S
FOR XML PATH ('Supplier'), ROOT ('Suppliers')
=================================================================
2) SELECT FROM XML FILE 2 TABLE FORMAT


DECLARE @x xml
SELECT @x = P
FROM OPENROWSET (BULK 'C:\IBMTP\AP_GD\AP_GD\AP_Notes\AP_SQL\2019\AP_SQL_XML_JSON_XML_Data.xml', SINGLE_BLOB) AS Products(P)

SELECT @x
=================================================================

>> READING SUB CATEGORY
SELECT @x = P
FROM OPENROWSET (BULK 'C:\IBMTP\AP_GD\AP_GD\AP_Notes\AP_SQL\2019\AP_SQL_XML_JSON_XML_Data.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
=================================================================