How do I get data into my table with a column of datatype XML?

• Robert W Marda

  SSChampion

  Points: 13413

  More actions

  November 11, 2005 at 10:23 am

  #165928

  I have created a schema and a table that has a column of datatype xml which uses the schema I created and have created code to insert a row into that table.  Unfortunately, I get an error, and don't know if the problem is with my schema (I suspect it is) or with the code that generates the XML to insert into the table.

  Here is the CREATE SCHEMA command:

  CREATE

  XML SCHEMA COLLECTION RobertMardasTestSchemaCollection AS

  N

  '<?xml version="1.0" encoding="UTF-16"?>

  <xsd:schema targetNamespace="PeopleInfo"

  xmlns ="PeopleInfo"

  elementFormDefault="qualified"

  attributeFormDefault="unqualified"

  xmlns:xsd="http://www.w3.org/2001/XMLSchema" >

  <xsd:complexType name="Test1" mixed="true" >

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

  <xsd:element name="TestDefinition1" type="xsd:string" />

  <xsd:element name="TestDefinitionID1" type="xsd:integer" />

  <xsd:element name="TestValue1" type="xsd:string" />

  </xsd:choice>

  </xsd:complexType>

  <xsd:element name="root">

  <xsd:complexType mixed="true">

  <xsd:sequence>

  <xsd:element name="step" type="Test1" minOccurs="1" maxOccurs="unbounded" />

  </xsd:sequence>

  </xsd:complexType>

  </xsd:element>

  </xsd:schema>'

  Here is the create table query:

  CREATE

  TABLE [dbo].[T1](

  [Col1] [int]

  NULL,

  [Col2] [xml]

  (CONTENT [dbo].[RobertMardasTestSchemaCollection]) NULL

  )

  ON [PRIMARY]

  And here is the query I am using:

  DECLARE @xml nvarchar(2000)

  SET @xml = (

     SELECT *

     FROM (

       SELECT  1 AS Tag,

         NULL AS Parent,

         NULL AS [Test1!1!TestDefinitionID1],

         NULL AS [Test1!1!TestValue1],

         NULL AS [Test1!1!TestDefinition1]

       FROM tblPeopleTests s

       INNER JOIN dbo.tblPeopleTestDefinitions d ON s.TestDefinitionID = d.TestDefinitionID

       WHERE s.PeopleID = 14538 AND s.Deleted = 0 AND d.Deleted = 0

       UNION ALL

       SELECT  1 AS Tag,

         NULL AS Parent,

         s.TestDefinitionID,

         s.TestValue,

         d.TestDefinition

       FROM tblPeopleTests s

       INNER JOIN dbo.tblPeopleTestDefinitions d ON s.TestDefinitionID = d.TestDefinitionID

       --INNER JOIN T1 ON t1.col1 = s.PeopleID

       WHERE s.PeopleID = 14538 AND s.Deleted = 0 AND d.Deleted = 0

    &nbsp d

     FOR XML EXPLICIT

       )

  INSERT INTO T1 (col1,col2)

  SELECT 14538 AS PeopleID, @xml AS TestXML

  And finally the error I get:

  Msg 6913, Level 16, State 1, Line 29

  XML Validation: Declaration not found for element 'Test1'. Location: /*:Test1[1]

  Sorry for the long post but I am trying to figure this out and any help will be much appreciated.

   

  Robert W. Marda
  Billing and OSS Specialist - SQL Programmer
  MCL Systems

• Robert W Marda

  SSChampion

  Points: 13413

  More actions

  November 11, 2005 at 2:24 pm

  #603517

  Ok.  Is there any tutorial that shows creating an XML schema and then using the various commands available in SQL Server 2005 to insert data into XML columns bound to that schema and such.

   

  The problem I am running into is that all the examples I am finding show you how to manipulate the data but don't show me the schema used so that I can see what the schema looks like and compare it to how data is being added to the XML column.

   

  Robert W. Marda
  Billing and OSS Specialist - SQL Programmer
  MCL Systems