XML in SQL Server cheat sheet

CREATE TABLE [dbo].[customers](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [name] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [Preferences] [xml] NULL,

 CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

insert into customers values(1,'herby','<Preferences />')

insert into customers values(2,'herby2','<Preferences />')

insert into customers values(3,'herby24','<Preferences />')

insert into customers values(4,'herby84','<Preferences />')

--manipulate and selects on xml data column

select id,Preferences.value('(/preferences/preference/@color)[2]','nvarchar(max)' )

from customers

where Preferences.exist('/preferences/preference[@color="blue" and @number ="2"]')=1

update customers

set preferences.modify('insert <preference  color ="blue" number ="2"  /> into (/preferences)[1]')

--data to xml conversions FOR XML

SELECT * FROM customers FOR XML AUTO, TYPE,root('doc')

SELECT * FROM customers FOR XML AUTO, TYPE,root('doc'), elements

SELECT

1 as tag,

null as parent,

name as [Person!1!name]--,

--id as [person!1!id!ELEMENT]

FROM customers FOR XML explicit, TYPE,root('doc')

--same thing using XQuery

SELECT (SELECT * FROM customers FOR XML AUTO, TYPE).query(

'<doc>{

   for $c in /customers

   return

     <Person name="{data($c/@name)}"/>

 }</doc>')

--xml text to data --attribute style

declare @text varchar(max)

declare @pointer int

set @text = '<doc>

  <Person name="herby" />

  <Person name="herby2" />

  <Person name="herby24" />

  <Person name="herby84" />

</doc>'

exec sp_xml_preparedocument @pointer out, @text

select name from openxml(@pointer,'doc/Person',0)

with ( name varchar(max)  '@name' )

exec sp_xml_removedocument @pointer

--xml text to data --element style

declare @text varchar(max)

declare @pointer int

set @text = '<doc>

  <Person><name>herby</name></Person>

  <Person><name>herby2</name></Person>

  <Person><name>herby24</name></Person>

  <Person><name>herby84</name></Person>

</doc>'

exec sp_xml_preparedocument @pointer out, @text

select * from openxml(@pointer,'doc/Person',1)

with ( name varchar(max)  'name' )

exec sp_xml_removedocument @pointer

--openxml works on xml data types also

declare @text xml

declare @pointer int

SELECT @text =

 (SELECT * FROM customers FOR XML AUTO, TYPE).query(

'<doc>{

   for $c in /customers

   return

     <Person name="{data($c/@name)}"/>

 }</doc>')

exec sp_xml_preparedocument @pointer out, @text

select name from openxml(@pointer,'doc/Person',0)

with ( name varchar(max)  '@name' )

exec sp_xml_removedocument @pointer