Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

RE: Expand / Collapse
Author
Message
Posted Friday, April 15, 2005 9:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:31 AM
Points: 117, Visits: 176

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'
 

 

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'
 

print '< select * from @myvariabletableContent >'
select * from @myvariabletableContent

 


BEGIN TRAN


INSERT INTO dbo.Cup
 (   
 materialname ,
 materialtrans  ,
 height ,
 heightunits  ,
 volume  , 
 volumeunits  ,
 haslid
 
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
   
  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>
'




Post #175025
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse