Insert Parent-Child related data using OPENXML

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bhavesh (Bhavesh_at_discussions.microsoft.com)
Date: 10/05/04 

Date: Tue, 5 Oct 2004 05:49:03 -0700

I have two classes Groups and Members and when I serialize (using
XmlSerializer) the object of Group class gives me the the xml string which I
am passing to the Stored procedure.

In the SP, I am trying to insert parent child records using openxml. My
Object Schema is such that I am not able to insert more than one child record
into the table.

The Schema for my class is as below:

public class Members
{
[XmlElement]
public int[] ID;
}

[Serializable]
public class Groups
{
public Members[] Group;
}

Below is my code for reference:

DROP TABLE GroupUsers
CREATE TABLE [dbo].[GroupUsers] (
    [GroupID] [int] NOT NULL ,
    [MemID] [int] NOT NULL
) ON [PRIMARY]
GO

DROP TABLE Groups
CREATE TABLE [dbo].[Groups] (
    [GroupID] [int] IDENTITY (1, 1) NOT NULL ,
    [CourseID] [int] NULL,
    [ImportID] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Groups] WITH NOCHECK ADD
    CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED
    (
        [GroupID]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GroupUsers] ADD
    CONSTRAINT [FK_GroupUsers_Groups] FOREIGN KEY
    ([GroupID]
    ) REFERENCES [dbo].[Groups] (
        [GroupID])
GO

DECLARE @doc int
exec sp_xml_preparedocument @doc OUTPUT,
'<Groups>
  <Group>
    <Members>
      <ID>1</ID>
      <ID>2</ID>
      <ID>3</ID>
    </Members>
  </Group>
  <Group>
    <Members>
      <ID>2</ID>
      <ID>3</ID>
      <ID>4</ID>
    </Members>
  </Group>
  <Group>
    <Members>
      <ID>4</ID>
      <ID>7</ID>
      <ID>9</ID>
    </Members>
  </Group>
</Groups>'

INSERT INTO Groups (CourseID, ImportID)
SELECT 30, XMLId
FROM OpenXML(@doc,'//Groups/Group', 2)
     WITH (XMLId int '@mp:id')

INSERT INTO GroupUsers (GroupID, MemID)
SELECT Groups.GroupID, ID
FROM OpenXML(@doc,'//Groups/Group/Members', 2)
     WITH (XMLParentID int '@mp:parentid',
           ID int) AS oxml
JOIN Groups ON oxml.XMLParentID = Groups.ImportID

EXEC sp_xml_removedocument @doc

select * from Groups
select * from GroupUsers

Can anybody tell me what I am doing wrong? Only one members gets inserted
against a group in the GroupUsers table instead of 3.

It's very URGENT!

Bhavesh

Relevant Pages

  • Re: Of Structs, Layouts, and Serialization
    ... Just use reflection to iterate across the members (sort ... members into an order though since order is undefined), serialize them ... roll-your-own metadata... ... know, outside of the BinaryFormatter, the only way to have a low ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: serializing non-serializable data members
    ... non-serializable data members? ... this problem arose when I tried to serialize RSAParameters, ... method which will save all the members of your class, may be it private ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: int not serializable?
    ... members, properties are serialized & deserialized later. ... bool and an int Member. ... I Serialize this ... > Regards Alexander ...
    (microsoft.public.dotnet.languages.csharp)
  • minOccurs from VB code - attribute?
    ... In a VB.Net webservice, after adding a data class, members of type String ... get a "minOccurs=0" attribute in the generated WSDL. ... public class MyWebService ...
    (microsoft.public.dotnet.framework.webservices)
  • minOccurs from VB code - attribute?
    ... In a VB.Net webservice, after adding a data class, members of type String ... public class MyWebService ... Public Sub TestFunc(byval t as TestData) ...
    (microsoft.public.dotnet.languages.vb)