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
