RE:

• sholliday

  SSCrazy

  Points: 2100

  More actions

  April 15, 2005 at 9:21 am

  #164085

  This was an excellent "quick tutorial" on XML.

  I would add, that there is something you can do with all that XML too.

  Especially since this is the "sqlserver"central website.

  Here is an example using the code supplied:

   

   

  if exists (select * from sysobjects

   where id = object_id('Content'))

   DROP TABLE Content

  if exists (select * from sysobjects

   where id = object_id('Cup'))

   DROP TABLE Cup

  CREATE TABLE dbo.Cup (

          cupid int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self explanatory

   materialname varchar(64) NULL ,

   materialtrans varchar(8) NULL ,

   height varchar(64) NULL ,

   heightunits varchar(64) NULL ,

   volume varchar(64) NULL , 

   volumeunits varchar(64) NULL ,

   haslid varchar(8) NULL ,  

   createdate datetime DEFAULT getDATE()

  )

  GO

  CREATE TABLE dbo.Content (

          contentid int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self explanatory

   cupid int NOT NULL  FOREIGN KEY (cupid) REFERENCES dbo.Cup(cupid), --

   contenttype varchar(64) NULL ,

   contentname varchar(64) NULL ,

   quantity int NOT NULL DEFAULT 0,

   createdate datetime DEFAULT getDATE()

  )

  GO

  print 'Tabled Created'

   

  if exists (select * from sysobjects

   where id = object_id('dbo.uspCreateCups') and sysstat & 0xf = 4)

   drop procedure dbo.uspCreateCups

  GO

  CREATE Procedure dbo.uspCreateCups

  (

   @xml_doc text

  )

  AS

  set nocount ON

  DECLARE @idoc int

  --Sample XML document

  --Create an internal representation of the XML document.

  exec sp_xml_preparedocument @idoc OUTPUT, @xml_doc

  -- SELECT statement using OPENXML rowset provider

  --Create a variable table to insert the values--this allows a bulk insert later

  DECLARE @myvariabletableCups table

  (

   materialname varchar(64) NULL ,

   materialtrans varchar(8) NULL ,

   height varchar(64) NULL ,

   heightunits varchar(64) NULL ,

   volume varchar(64) NULL , 

   volumeunits varchar(64) NULL ,

   haslid varchar(8) NULL

  )

  DECLARE @myvariabletableContent table

  (

   contenttype varchar(64) NULL ,

   contentname varchar(64) NULL ,

   quantity int NOT NULL DEFAULT 0

  )

  /*

  <CUP>

  <MATERIAL transparent="yes">glass</MATERIAL>

  <HEIGHT units="inches">6</HEIGHT>

  <VOLUME units="ounces">16</VOLUME>

  <CONTENTS>

   <SOLID qty="2">ice cube</SOLID>

   <SOLID qty="1">straw</SOLID>

   <LIQUID qty="3" units="ounces">water</LIQUID>

   <OTHER qty="0"/>

  </CONTENTS>

  <LID>yes</LID>

  </CUP>

  */

   

   

  --but only the needed data is actually inserted into the real tables

  INSERT INTO @myvariabletableCups

  SELECT

   materialname ,

   materialtrans  ,

   height ,

   heightunits  ,

   volume  , 

   volumeunits  ,

   haslid

  FROM   

   OPENXML (@idoc, '/CUP',2) --The "2" means something.  See Books Online.

          WITH

   ( 

    materialname varchar(64) './MATERIAL' ,

   materialtrans varchar(64) './MATERIAL/@transparent' ,

   height varchar(64)  './HEIGHT',

   heightunits varchar(64)  './HEIGHT/@units',

   volume varchar(64) './VOLUME',

   volumeunits varchar(64)   './VOLUME/@units',

   haslid varchar(8)   './LID'

  &nbsp

   

  print '< select * from @myvariabletableCups >'

  select * from @myvariabletableCups

   

   

  INSERT INTO @myvariabletableContent

  SELECT

   'SOLID, not perfect example with in-consistent Xpath'  ,

   contentname  ,

   quantity

  FROM   

   OPENXML (@idoc, '/CUP/CONTENTS/SOLID',2) --The "2" means something.  See Books Online.

          WITH

   ( 

    contenttype varchar(64)  ,

   contentname varchar(64) '.' ,

   quantity varchar(64)  './@qty'

  &nbsp

  print '< select * from @myvariabletableContent >'

  select * from @myvariabletableContent

   

  BEGIN TRAN

  INSERT INTO dbo.Cup

   (   

   materialname ,

   materialtrans  ,

   height ,

   heightunits  ,

   volume  , 

   volumeunits  ,

   haslid

  &nbsp

  SELECT

   materialname ,

   materialtrans  ,

   height ,

   heightunits  ,

   volume  , 

   volumeunits  ,

   haslid

  FROM

   @myvariabletableCups

  DECLARE @cupJustInsertedIdentity int

  SELECT @cupJustInsertedIdentity = IDENT_CURRENT('dbo.Cup')

  if (@cupJustInsertedIdentity IS NOT NULL)

   BEGIN

    INSERT INTO dbo.Content

     (

     cupid ,

     contenttype ,

     contentname  ,

     quantity

    &nbsp

    SELECT

     @cupJustInsertedIdentity ,

     contenttype ,

     contentname  ,

     quantity

    

    FROM   

     @myvariabletableContent

   END

  IF @@ERROR <> 0   

    BEGIN   

      RAISERROR('insert error', 16, 1) 

    ROLLBACK Transaction

   END

  ELSE

   BEGIN

    COMMIT TRAN

   END

   

  print '< select * from dbo.Cup >'

  select * from dbo.Cup

  print '< select * from dbo.Content >'

  select * from dbo.Content

  SET NOCOUNT OFF

  GO

  print 'Procedure Created'

   

   

   

   

  Now, to use it:

   

  EXEC dbo.uspCreateCups

  '

  <?xml version="1.0"?>

  <CUP>

  <MATERIAL transparent="yes">glass</MATERIAL>

  <HEIGHT units="inches">6</HEIGHT>

  <VOLUME units="ounces">16</VOLUME>

  <CONTENTS>

   <SOLID qty="2">ice cube</SOLID>

   <SOLID qty="1">straw</SOLID>

   <LIQUID qty="3" units="ounces">water</LIQUID>

   <OTHER qty="0"/>

  </CONTENTS>

  <LID>yes</LID>

  </CUP>

  '