Insert xml element contents(which is also xml tags) into column in SQL Server 2005

Question

For a table schema like below

CREATE TABLE [dbo].[Employee](
    [EmployeeId] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Location] [nvarchar](50) NOT NULL,
    [Skills] [xml] NOT NULL,
    [Projects] [nvarchar](400) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

When inserting data into table, i want to insert only child tags of <SkillSet> but seems <SkillSet> along with it's child elements inserts into Skill xml columns.

declare @doc NVARCHAR(MAX)

declare @idoc INT

select @doc = '<Request Type="InsertEmployee" CRUD="C">
    <Employee>
      <EmployeeId>1</EmployeeId>
      <Name>Deeptechtons</Name>
      <Location>USA</Location>
        <SkillSet>
        <Skill>C#</Skill>
        <Skill>SQL Server 2005</Skill>
        <Skill>ASP.net</Skill>
        </SkillSet>
      <Projects>
        <Project>LowBin</Project>
        <Project>Maskin</Project>
      </Projects>
    </Employee>
  </Request>'

exec sp_xml_preparedocument @idoc output,@doc

insert into Employee (
                        EmployeeId
                        ,[Name]
                        ,Location
                        ,Skills,
                        Projects
                     )
            SELECT      NEWID()
                        ,[Name]
                        ,Location
                        ,Skills
                        ,Projects
            FROM OPENXML(@idoc,'Request/Employee',2)
            WITH
                    (
                        [Name] NVARCHAR(50)
                        ,Location   NVARCHAR(50)
                        ,Skills XML 'SkillSet'
                        ,Projects NVARCHAR(400)
                    )

exec sp_xml_removedocument @idoc

Questions

How to insert only child elements of <Skillset> rather than whole tag and its children.
I expected Projects also to be inserted same as did but only Lowbin content of first Project tag is inserted. Can you correct my code.

Mikael Eriksson · Accepted Answer · 2012-01-04 16:39:10Z

1

Since you are on SQL Server 2005 you can make use of the XML data type and do like this instead.

declare @doc xml

select @doc = '<Request Type="InsertEmployee" CRUD="C">
    <Employee>
      <EmployeeId>1</EmployeeId>
      <Name>Deeptechtons</Name>
      <Location>USA</Location>
        <SkillSet>
        <Skill>C#</Skill>
        <Skill>SQL Server 2005</Skill>
        <Skill>ASP.net</Skill>
        </SkillSet>
      <Projects>
        <Project>LowBin</Project>
        <Project>Maskin</Project>
      </Projects>
    </Employee>
  </Request>'

insert into Employee (
                        EmployeeId
                        ,[Name]
                        ,Location
                        ,Skills,
                        Projects
                     )
select newid(),
       T.N.value('Name[1]',  'nvarchar(50)'),
       T.N.value('Location[1]',  'nvarchar(50)'),
       T.N.query('SkillSet/*'),
       cast(T.N.query('Projects/*') as nvarchar(400))
from @doc.nodes('/Request/Employee') as T(N)

Result:

BC76E37C-0C0D-4B7B-92FD-0F7807F9204B 
Deeptechtons 
USA 
<Skill>C#</Skill><Skill>SQL Server005</Skill>Skill>ASP.net</Skill>
<Project>LowBin</Project><Project>Maskin</Project>

Update:

If you really want to use OPENXML for some reason you can use this.

insert into Employee (
                        EmployeeId
                        ,[Name]
                        ,Location
                        ,Skills,
                        Projects
                     )
            SELECT      NEWID()
                        ,[Name]
                        ,Location
                        ,Skills.query('SkillSet/*')
                        ,cast(Projects.query('Projects/*') as nvarchar(max))
            FROM OPENXML(@idoc,'Request/Employee',2)
            WITH
                    (
                        [Name] NVARCHAR(50)
                        ,Location   NVARCHAR(50)
                        ,Skills XML 'SkillSet'
                        ,Projects XML
                    )

edited Jan 4, 2012 at 16:39

answered Jan 4, 2012 at 16:10

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Deeptechtons Over a year ago

do i get to lose anything by declaring @doc to be xml datatype, also could u provide a reference to T(N) what does it mean how does it work

Mikael Eriksson Over a year ago

@Deeptechtoons The output is the same but there might be a difference in performance. Might be faster or slower but it is much easier to do what you want. Nodes() shreds the XML into rows and T is the table alias that holds the shredded XML and N is the column name. You can just name them whatever you like.

Deeptechtons Over a year ago

Sorry to disturb you but i think you are right man who can answer this well stackoverflow.com/questions/8740826/…

Collectives™ on Stack Overflow

Insert xml element contents(which is also xml tags) into column in SQL Server 2005

Questions

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Questions

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related