tigriswoods
asked on
How to Create and Update XML column?
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?
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_
[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
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>'
ASKER
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!
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!
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
ASKER
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!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SET GoodAdvice = GoodAdvice.modify('insert element recommendation {Do not quit your day job} as first into (//root)[1]')