Re: Shredding XML
- From: Buehler <tlbb123_NS@xxxxxxxxx>
- Date: Fri, 25 Aug 2006 01:48:13 GMT
Thanks for your help Dan!
The stored procedure code below is what I came up with to build a
custom-shredding routine that can be used against many different
tables. The Idea is to have an XMLMap table that the routine can read
to determine which field values should be extracted from an XML doc
and placed into 1...n keyfield columns in the MAINxx table(s).
The stored procedure works, but the process of dynamically gathering
the Xpaths and executing the Value Method queries seems not at all
elegant and I'm wondering if I'm doing it the best/only way, or if
there is a better way someone might suggest. I guess I'm asking for a
code review and if someone has a little bit of time, any and all
suggestions are gratefully appreciated. (Thanks again to everyone who
helped get me this far)
The components are an XMLMap table of
/****** Object: Table [dbo].[XMLMap] Script Date: 08/24/2006
20:58:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[XMLMap](
[TableName] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL,
[XPath] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL,
[FieldName] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL,
[Result] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_XMLMap_Result] DEFAULT ('NUL')
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'What
to do on XPath Error - NULL, IGNORE, ERROR are options' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'XMLMap',
@level2type=N'COLUMN',@level2name=N'Result'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Maps
fields in XML document to tables key fields.' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'XMLMap'
GO
ALTER TABLE [dbo].[XMLMap] WITH CHECK ADD CONSTRAINT [CK_XMLMap]
CHECK (([Result]='ERR' OR [Result]='IGN' OR [Result]='NUL'))
GO
ALTER TABLE [dbo].[XMLMap] CHECK CONSTRAINT [CK_XMLMap]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Allow Result field to only contain NUL, IGN or ERR' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'XMLMap',
@level2type=N'CONSTRAINT',@level2name=N'CK_XMLMap'
=================================================
along with 1...n tables such as:
=================================================
/****** Object: Table [dbo].[Main] Script Date: 08/24/2006
21:01:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Main](
[id] [tinyint] IDENTITY(1,1) NOT NULL,
[KeyColumn1] [nvarchar](150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[KeyColumn2] [numeric](18, 0) NULL,
[KeyColumn3] [datetime] NULL,
[XMLText] [xml] NULL,
CONSTRAINT [PK_Main] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
==============================================
the 'insert' stored procedure which will call the mnXMLShred stored
procedure........
=============================================
/****** Object: StoredProcedure [dbo].[mnInsertXML] Script Date:
08/24/2006 21:31:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=============================================
-- Description: Insert Data into given column, given table name and
XML as parameters.
-- This proc will insert the record, then call the generic procedure
XMLShred
-- =============================================
CREATE PROCEDURE [dbo].[mnInsertXML]
-- Add the parameters for the stored procedure here
@tablename varchar(128),
@xml xml
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @scopeIdent int;
-- Insert statements for procedure here
INSERT INTO dbo.Main (XMLText) values (@xml);
-- get the identity value from the row just inserted
SET @scopeIdent = SCOPE_IDENTITY();
select @scopeIdent;
exec mnXMLShred @scopeIdent, @tablename, @xml;
select * from main;
END
============================================
and the re-useable stored procedure is:.........
============================================
/****** Object: StoredProcedure [dbo].[mnXMLShred] Script Date:
08/24/2006 21:15:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[mnXMLShred]
@ident int,
@tablename varchar(128),
@xml xml
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE keycolumns_cursor CURSOR LOCAL
FOR select xPath,FieldName from dbo.XMLMap m where m.tablename
= @tablename
DECLARE @xPath varchar(1000)
DECLARE @FieldName char(10)
DECLARE @keyColumnValue varchar(1000)
DECLARE @sqltextUpdate varchar(max)
DECLARE @sqltextValue varchar(max)
DECLARE @cmd nvarchar(max)
-- get all the key column names from the XMLMAP table
OPEN keycolumns_cursor
FETCH NEXT FROM keycolumns_cursor
INTO @xPath, @FieldName
WHILE @@FETCH_STATUS = 0
BEGIN
-- get the keycolumn value from the xml
-- build command that includes the setting of @xml
declare @keyout nvarchar(max)
SET @cmd = 'DECLARE @xml xml;
SET @xml=N''' + CAST(@xml AS NVARCHAR(MAX)) +
''';'
+ 'SELECT @key = @xml.value(''(' + @xPath +
')[1]'',''varchar(max)'')'
EXEC sp_executesql @cmd, N'@key varchar(max) output',
@key=@keyout output
-- build the update statement
SET @sqltextUpdate = 'Update ' + @tablename + ' set ' +
@FieldName + ' =''' + @keyout + ''' where ' + @tablename+'.id = ' +
cast(@ident as varchar(128))
EXEC(@sqltextUpdate)
FETCH NEXT FROM keycolumns_cursor
INTO @xPath, @FieldName
END
CLOSE keycolumns_cursor
DEALLOCATE keycolumns_cursor
END
=========================================
and some test data for XMLMAP
=========================================
INSERT INTO [dbo].[XMLMap]
([TableName]
,[XPath]
,[FieldName]
,[Result])
VALUES
('Main'
,'/document/fields/field[@fieldname = "VarCharValue"]'
,'KeyColumn1'
,'NUL')
INSERT INTO [dbo].[XMLMap]
([TableName]
,[XPath]
,[FieldName]
,[Result])
VALUES
('Main'
,'/document/fields/field[@fieldname = "NumericValue"]'
,'KeyColumn2'
,'NUL')
INSERT INTO [dbo].[XMLMap]
([TableName]
,[XPath]
,[FieldName]
,[Result])
VALUES
('Main'
,'/document/fields/field[@fieldname = "DateValue"]'
,'KeyColumn3'
,'NUL')
====================================================
and finally some test xml I'm inserting into MAINxx
===================================================
mnInsertXML 'MAIN', '<document><fields>
<field fieldname="VarCharValue" changeId="932749324" >Smith,
Bob</field>
<field fieldname="NumericValue" changeId="932749324"
333233344422211111</field><field fieldname="DateValue" changeId="932749324" >08-26-2006</field>
<field fieldname="State" changeId="932749324" >IL</field>
<field fieldname="Phone" changeId="932749324" >888-555-1212</field>
</fields></document>'
.
- References:
- Re: Shredding XML
- From: Buehler
- Re: Re: Shredding XML
- From: Dan Sullivan
- Re: Shredding XML
- Prev by Date: Re: Shredding XML
- Next by Date: XQuery invalid character
- Previous by thread: Re: Re: Shredding XML
- Next by thread: Re: Shredding XML
- Index(es):
Relevant Pages
|
