Links to these items on MSDN; (XML DML)

INSERT: http://msdn.microsoft.com/en-us/library/ms175466.aspx

DELETE: http://msdn.microsoft.com/en-us/library/ms190254.aspx

REPLACE VALUE OF: http://msdn.microsoft.com/en-us/library/ms190675.aspx

 

/*****************************************************
           .query() EXAMPLES

      querying a typed and untyped XML column

      CASE SENSITIVE for the xml elements
*****************************************************/

--syntax: xml_obj.query ( xquery )

USE AdventureWorks --2005 version
GO
--returns only AnnualSales
SELECT  SalesPersonID,
        Demographics.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";(/ns:StoreSurvey/ns:AnnualSales)') AS AnnualSales
FROM    Sales.Store ;

--have to declare the namespace for each .query - better to set the default with XMLNAMESPACES as used below
SELECT  SalesPersonID,
        Demographics.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";(/ns:StoreSurvey/ns:AnnualSales)') AS AnnualSales,
        Demographics.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";(/ns:StoreSurvey/ns:AnnualRevenue)') AS AnnualSales
FROM    Sales.Store ;

WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.query('(/StoreSurvey/AnnualSales)') AS AnnualSales,
        Demographics.query('(/StoreSurvey/AnnualRevenue)') AS AnnualSales
FROM    Sales.Store ;

--uses "data" function to get just the value from the element
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT SalesPersonID, Demographics.query('data(/StoreSurvey/AnnualSales)') FROM Sales.Store ; 

--GENERATES AN ERROR
--change the case of one letter and it errors e.g AnnualSales to annualSales
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.query('(/StoreSurvey/annualSales)') AS AnnualSales
FROM    Sales.Store ;

--all elements under StoreSurvey
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.query('(//StoreSurvey)') AS StoreSurvey
FROM    Sales.Store ;

--all elements under AnnualSales
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.query('(//AnnualSales)') AS AnnualSales
FROM    Sales.Store ;

--pulls all numbers (note the change of schema)
--Frances has no number in her xml so record is blank
--NULL is for those columns with no XML
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' )
SELECT  FirstName,
        AdditionalContactInfo.query('(//number)') AS ContactNumber
FROM    Person.Contact ;

--pulls first number only
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' )
SELECT  FirstName,
        AdditionalContactInfo.query('(//number)[1]') AS ContactNumber
FROM    Person.Contact ;

--pulls first street name only
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' )
SELECT  FirstName,
        AdditionalContactInfo.query('(//Street)[1]') AS ContactNumber
FROM    Person.Contact ;

--this is an untyped column
--no need to specify a namespace
SELECT  PostTime,
        Event,
        Object,
        xmlevent.query('/EVENT_INSTANCE/TSQLCommand/CommandText') AS xmlEvent
FROM    dbo.DatabaseLog ;

-- the xml can even be cast as character text but it's not good as it created a lot of entitized characters
SELECT  PostTime,
        Event,
        Object,
        CAST(xmlevent.query('/EVENT_INSTANCE/TSQLCommand/CommandText') AS NVARCHAR(MAX)) AS xmlEvent
FROM    dbo.DatabaseLog ;


/********************************************************************************
           .value() EXAMPLES

      querying a typed and untyped XML column

      CASE SENSITIVE for the xml elements

      requires to return singleton value so specify [1] in query
      specify the datatype for the output, value requires two arguments
********************************************************************************/

--syntax: xml_obj.value ( xquery, data_type )

--returns only AnnualSales
--attribute must have a integer value to find i.e. 1, 2 etc
SELECT  SalesPersonID,
        Demographics.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";(/ns:StoreSurvey/ns:AnnualSales)[1]',
                           'money') AS AnnualSales
FROM    Sales.Store ;

--with the default namespace declared, path from root
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.value('(/StoreSurvey/AnnualSales)[1]', 'money') AS AnnualSales
FROM    Sales.Store ;

--GENERATES AN ERROR because of case sensitivity (despite the SQL Server not being case sensitive collation)
--change the case of one letter and it errors e.g AnnualSales to annualSales
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.value('(/StoreSurvey/annualSales)[1]', 'money') AS AnnualSales
FROM    Sales.Store ;

--more than 1 column
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.value('(/StoreSurvey/AnnualSales)[1]', 'money') AS AnnualSales,
        Demographics.value('(/StoreSurvey/AnnualRevenue)[1]', 'money') AS AnnualRevenue
FROM    Sales.Store ;

--GENERATES ERROR
--cannot specify all elements under StoreSurvey as result should be a single value
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.value('(//StoreSurvey)',
                           'money') AS StoreSurvey
FROM    Sales.Store ;

--Can specify all values from AnnualSales when [1] is used
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.value('(//AnnualSales)[1]', 'money') AS AnnualSales
FROM    Sales.Store ;

--pulls first number only
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' )
SELECT  FirstName,
        AdditionalContactInfo.value('(//number)[1]', 'varchar(20)') AS ContactNumber
FROM    Person.Contact ;

--pulls second number only
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' )
SELECT  FirstName,
        AdditionalContactInfo.value('(//number)[2]', 'varchar(20)') AS ContactNumber
FROM    Person.Contact ;

--this is an untyped column
--all objects under databasename
SELECT  PostTime,
        Event,
        Object,
        xmlevent.value('(//DatabaseName)[1]', 'varchar(50)') AS xmlEvent_value
FROM    dbo.DatabaseLog ;

--specify the whole path
--have to specify the singleton value every time... [1] or [2] or [3] etc
SELECT  PostTime,
        Event,
        Object,
        xmlevent.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(50)') AS xmlEvent_value
FROM    dbo.DatabaseLog ;


/********************************************************************************
           --.exist() EXAMPLES

      querying a typed and untyped XML column

      CASE SENSITIVE for the xml elements

      returns a 1 for a yes (record exists) and 0 for no record exists
      also can return NULL if the instance is null
********************************************************************************/

USE AdventureWorks --2005 version
GO

--wrap in brackets inside the quotes or not, doesn't matter.
SELECT  PostTime,
        Event,
        Object,
        xmlevent.exist('(/EVENT_INSTANCE/SPID)') AS xmlEvent
FROM    dbo.DatabaseLog ;

SELECT  PostTime,
        Event,
        Object,
        xmlevent.exist('/EVENT_INSTANCE/SPID') AS xmlEvent --no brackets
FROM    dbo.DatabaseLog ;


SELECT  PostTime,
        Event,
        Object,
        xmlevent.exist('//SPID') AS xmlEvent
FROM    dbo.DatabaseLog ;

--uses a value comparison -eq for equals
SELECT  xmlevent.exist('/EVENT_INSTANCE/TSQLCommand/SetOptions[@ANSI_NULLS eq "ON"]')
FROM    dbo.DatabaseLog ;

--brackets required inside quotes for namespace items
--existance of first number only
SELECT  FirstName,
        AdditionalContactInfo.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";(//ns:number)[1]') AS ContactNumber
FROM    Person.Contact ;

--existance second number only
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' )
SELECT  FirstName,
        AdditionalContactInfo.exist('(//number)[2]') AS ContactNumber
FROM    Person.Contact ;

--returns existance of AnnualSales
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        Demographics.exist('(/StoreSurvey/AnnualSales)[1]') AS AnnualSales
FROM    Sales.Store ;

/********************************************************************************
           --.nodes() EXAMPLES

      querying a typed and untyped XML column

      CASE SENSITIVE for the xml elements
     requires aliasing of virtual table and column
     use cross apply to get nodes from xml column in table
********************************************************************************/

--syntax: xml_obj.nodes ( xquery ) AS table ( column )
-- not required here but ./ is specifying context node - is only one level
SELECT  col.value('./EventType[1]', 'varchar(100)') AS EventType,
        col.value('./PostTime[1]', 'varchar(50)') AS PostTime,
        col.value('./ObjectName[1]', 'varchar(100)') AS ObjectName
FROM    DatabaseLog
        CROSS APPLY XmlEvent.nodes('//EVENT_INSTANCE') AS my_table ( col )
WHERE   Object = 'ProductPhoto' ;

--same again with .exist clause as part of WHERE clause
--the .exist of the where clause requires the full path i.e. cannot use ./objectname
SELECT  col.value('EventType[1]', 'varchar(100)') AS EventType,
        col.value('PostTime[1]', 'varchar(50)') AS PostTime,
        col.value('ObjectName[1]', 'varchar(100)') AS ObjectName
FROM    DatabaseLog
        CROSS APPLY XmlEvent.nodes('//EVENT_INSTANCE') AS my_table ( col )
WHERE   XmlEvent.exist('EVENT_INSTANCE/ObjectName[. = "ProductPhoto"]') = 1 ;


WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )
SELECT  SalesPersonID,
        col.value('(/StoreSurvey/AnnualSales)[1]', 'money') AS AnnualSales,
        col.value('(/StoreSurvey/Brands)[1]', 'varchar(20)') AS Brands
FROM    Sales.Store
        CROSS APPLY Demographics.nodes('//StoreSurvey') AS my_table ( col )
WHERE   Demographics.exist('StoreSurvey/Brands[.="AW"]') = 1 ;

/********************************************************************************
           .modify() EXAMPLES

      XML DML on typed and untyped XML column

      CASE SENSITIVE for the xml elements

    XML DML covers DELETE, INSERT and update by way of "REPLACE VALUE OF"
********************************************************************************/


--need to get detail of schema collection first to determine data type.
--run this to get [ XML_schema_collection_name ] and [ Namespace ] so that xml_schema_namespace can be populated
SELECT  s.name AS SchemaName,
        o.name AS TableName,
        c.name AS ColumnName,
        sc.name AS XMLSchemaCollectionName,
        sn.name AS Schema_Namespace
FROM    sys.objects AS o
        INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
        INNER JOIN sys.xml_schema_collections AS sc ON s.schema_id = sc.schema_id
        INNER JOIN sys.xml_schema_namespaces AS sn ON sc.xml_collection_id = sn.xml_collection_id
        INNER JOIN sys.columns AS c ON o.object_id = c.object_id
                                       AND c.xml_collection_id = sc.xml_collection_id
WHERE   o.name = 'store'
        AND c.name = 'demographics'
        

--use results to populate this
xml_schema_namespace( Relational_schema , XML_schema_collection_name , [ Namespace ] )

--use results of this in update statement of typed xml
select xml_schema_namespace( 'Sales' , 'StoreSurveySchemaCollection' , 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' )


BEGIN TRAN;
SELECT * FROM Sales.Store WHERE CustomerID = 1

--replace an existing value
UPDATE Sales.Store SET Demographics.modify('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
replace value of (/ns:StoreSurvey/ns:Internet)[1] with ns:InternetType("DSL")')
WHERE CustomerID = 1

SELECT * FROM Sales.Store WHERE CustomerID = 1

--COMMIT
--ROLLBACK

--specify text() for untyped element
UPDATE dbo.DatabaseLog SET XmlEvent.modify('replace value of (/EVENT_INSTANCE/ObjectType/text())[1] with "Table"')
WHERE DatabaseLogID = 1



--DELETE
BEGIN TRAN;
SELECT * FROM Sales.Store WHERE CustomerID = 1

UPDATE Sales.Store SET Demographics.modify('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
delete (/ns:StoreSurvey/ns:Internet)[1]')
WHERE CustomerID = 1

SELECT * FROM Sales.Store WHERE CustomerID = 1
--COMMIT
--ROLLBACK

UPDATE dbo.DatabaseLog SET XmlEvent.modify('delete (/EVENT_INSTANCE/TSQLCommand/CommandText)[1]')
WHERE DatabaseLogID = 1


--INSERT
UPDATE dbo.DatabaseLog SET XmlEvent.modify('insert 
 CREATE TABLE [dbo].[ErrorLog](
[ErrorLogID] [int] IDENTITY (1, 1) NOT NULL,
[ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime] DEFAULT (GETDATE()),
[UserName] [sysname] NOT NULL, 
[ErrorNumber] [int] NOT NULL, 
[ErrorSeverity] [int] NULL, 
[ErrorState] [int] NULL, 
[ErrorProcedure] [nvarchar](126) NULL, 
[ErrorLine] [int] NULL, 
[ErrorMessage] [nvarchar](4000) NOT NULL
) ON [PRIMARY];

after (EVENT_INSTANCE/TSQLCommand/SetOptions)[1]')
WHERE DatabaseLogID = 1



UPDATE Sales.Store SET Demographics.modify('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
insert T1 after (ns:StoreSurvey/ns:Brands)[1]')
WHERE CustomerID = 1


Links to these items on MSDN; (XML DML)

INSERT: http://msdn.microsoft.com/en-us/library/ms175466.aspx

DELETE: http://msdn.microsoft.com/en-us/library/ms190254.aspx

REPLACE VALUE OF: http://msdn.microsoft.com/en-us/library/ms190675.aspx