Solved

How to Create and Update XML column?

Posted on 2008-10-09
7
3,355 Views
Last Modified: 2013-11-11
I like the entire concept of xml columns but I am really new to XML.  I'm trying to start out with a simple case but I can't seem to get it to work.  

What I want to do is to create a column that stores zero to many recommendations, for instance something like this:
<root>
<recommendation>Work hard, play hard</recommendation>
<recommendation>Floss the teeth you want to keep</recommendation>
<recommendation>Wash behind your ears</recommendation>
</root>
I have created a schema collection ( - have I done it correctly ?  - this could be the cause of the problem.  I've had trouble finding good examples for the schema collection).  I've attached this as a code snippet.

Next I created a column called GoodAdvice in my Table (called Table_1)

SET ANSI_NULLS ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [AdvisorName] [nchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [GoodAdvice] [xml](CONTENT [dbo].[recommendations]) NULL
) ON [PRIMARY]

(In reality, Table_1 already existed, and I am adding the new GoodAdvice column to it.)

Now.  I have a row that has id and AdvisorName already populated.

id=1, AdvisorName="Doctor Who"

and I want to start putting recommendations from Doctor Who into the table.  What is the right syntax for this?  I've tried to do this in various ways, and most recently tried the following with no success:

UPDATE Table_1
   SET GoodAdvice.modify('insert element  recommendation {Do not quit your day job} as first into (//root)[1]')
   WHERE Table_1.id = 1

I get this error:  XQuery [Table_1.GoodAdvice.modify()]: "}" was expected.



Being new to this is frustrating. What am I doing wrong?







CREATE XML SCHEMA COLLECTION [dbo].[recommendations] AS N

'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">

<xsd:element name="root">

	<xsd:complexType>

		<xsd:complexContent>

			<xsd:restriction base="xsd:anyType">

				<xsd:choice minOccurs="0" maxOccurs="unbounded">

					<xsd:element name="recommendation" type="xsd:string" minOccurs="0" />

				</xsd:choice></xsd:restriction>

			</xsd:complexContent>

		</xsd:complexType>

	</xsd:element>

</xsd:schema>'

Open in new window

0
Comment
Question by:tigriswoods
  • 3
  • 3
7 Comments
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22682916
Try with

SET GoodAdvice = GoodAdvice.modify('insert element  recommendation {Do not quit your day job} as first into (//root)[1]')
0
 

Author Comment

by:tigriswoods
ID: 22683038
Hello,  
I tried your suggestion and received this error:

Illegal use of xml data type method 'modify'. A non-mutator method is expected in this context.

Other suggestions?

Thanks!
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22683141
See an approach
CREATE TABLE TestXML(

	Id INT IDENTITY(1, 1) NOT NULL,

	Doc XML NOT NULL

)

GO

INSERT INTO TestXML (Doc) VALUES ('

<root>

	<child id="1">Value 1</child>

	<child id="2">Value 2</child>

	<child id="3">Value 3</child>

	<child id="4">Value 4</child>

</root>

')

GO

SELECT Doc FROM TestXML

GO

UPDATE TestXML

SET Doc.modify('insert <child id="5">Value 5</child> before (root/child[@id = "4"])[1]')

GO

SELECT Doc FROM TestXML

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tigriswoods
ID: 22683749
From the reading I have done, I  believe that I want to  use an XML typed column so that I will be able to query the fields.  If I'm not mistaken, using a DOC xml field will not permit me to do this?  Can you help me create and update the xml column so that I will be able to query  and index the fields? Thanks!
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22703164
Is not required to have a typed xml column
CREATE TABLE TestXML(

	Id INT IDENTITY(1, 1) NOT NULL,

	Doc XML NOT NULL,

	CONSTRAINT pkTestXML PRIMARY KEY (Id)

)

GO

CREATE PRIMARY XML INDEX ixTestXML

ON TestXML (Doc)

GO

CREATE XML INDEX ixTestXML_Childs

ON TestXML (Doc)

USING XML INDEX ixTestXML

FOR PATH

Open in new window

0
 

Accepted Solution

by:
tigriswoods earned 0 total points
ID: 22955849
I finally figured this one out.
Here's how I inserted the first one:

update Table1 set  GoodAdvice.modify('
insert
<recommendation>First recommendation</recommendation>
as first into
(/root)[1]
')

And here's how I added additional ones:

update Table1 set  GoodAdvice.modify('
insert
<recommendation>Another recommendation</recommendation>
as last into
(/root)[1]
')
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now