Site Search:
Sign in | Join | Help
4Penny.net

SQL Server (T-SQL)

Comments and notes on SQL Server 2000, 2005, and T-SQL

January 2008 - Posts

  • How to read an attribute from an XML field in SQL Server 2005

    This tip explains how to read an XML attribute from an XML field in sql server. Or, put another way, if we have this data:

     <Receivable documentNumber="PYMNT000000000012    " documentDate="12/13/2007">
  <Type>PAYMENT</Type>
  <CashType>CHECK</CashType>
  <Description />
  <CheckNumber />
  <Amount>3.00000</Amount>
  <AppliedAmount>3.00000</AppliedAmount>
  <UnappliedAmount>0.00000</UnappliedAmount>
  <Items />
</Receivable>

    We want to use SQL Server 2005 to retrieve the document number (PYMNT000000000012)

    The code looks like this:

    --create an xml variable, and then read an xml document into it. 
declare @x xml
declare @y as varchar(1000)
select @y =      '<Receivable documentNumber="PYMNT000000000012    " documentDate="12/13/2007">'
select @y = @y + '  <Type>PAYMENT</Type>'
select @y = @y + '  <CashType>CHECK</CashType>'
select @y = @y + '  <Description />'
select @y = @y + '  <CheckNumber />'
select @y = @y + '  <Amount>3.00000</Amount>'
select @y = @y + '  <AppliedAmount>3.00000</AppliedAmount>'
select @y = @y + '  <UnappliedAmount>0.00000</UnappliedAmount>'
select @y = @y + '  <Items />'
select @y = @y + '</Receivable>'
select @x = @y


--SQL 2000 CODE


-- Initialize XML handle
DECLARE @hdoc INT    
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
 
 -- select the records
SELECT x.documentNumber 
 FROM OPENXML ( @hdoc, '/Receivable', 1 ) WITH (
  documentNumber VARCHAR(20) '@documentNumber'
  ) AS x
 
 -- Release XML handle
 EXEC sp_xml_removedocument @hdoc


--SQL 2005 CODE


SELECT 
  x.header.value('@documentNumber[1]', 'varchar(20)') AS OrderNumber,
  x.header.value('Typex[1]', 'varchar(20)') AS Typex2
 FROM @x.nodes('//Receivable') AS x(header)

    Posted Jan 18 2008, 03:03 PM by Steve Gray
    [Edit Tags]

  • Script to create an UPDATE statement

    This script will create an update statement

     -- =============================================
-- 
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = N'sp_update' 
    AND    type = 'P')
    DROP PROCEDURE sp_update
GO


CREATE PROCEDURE sp_update 
-- sp_update 'cn20100'


@tablename varchar(50)



AS


set nocount on
 
declare @output varchar(8000)
declare @fieldTypeVB varchar(50)
declare @fieldName varchar(500)
declare @fieldType varchar(50)
declare @paramName varchar(255)
declare @paramList varchar(8000)
declare @paramListVB varchar(8000)
declare @paramListHelper varchar(8000)
declare @vchrProcName varchar(255),
 @vchrSetList varchar(8000),
 @intColStat int


set @output = ''


select
 @paramList = '',
 @vchrSetList = '',
 @paramListVB = '',
 @paramListHelper = '',
 @output = '',
 @vchrProcName = '_4P_' + upper(@tablename) + '_UPD',
 @tableName = upper(@tableName)



print 'IF EXISTS (SELECT name '
print '    FROM   sysobjects '
print '    WHERE  name = N' + char(39) + @tablename + char(39)
print '        AND type = ' + char(39) + 'P' + char(39) + ')'
print '    DROP PROCEDURE ' + @vchrProcName
print 'GO'
print ''
print 'CREATE PROCEDURE ' + @vchrProcName


declare curFields CURSOR for
select syscolumns.name,
  dbo.f_4P_columnType (xusertype,length, xprec , xscale),
  dbo.f_4P_columnTypeVB (xusertype),
  '@' + syscolumns.name,
  syscolumns.colstat
 from sysobjects
  left join syscolumns on syscolumns.id = sysobjects.id
 where sysobjects.name = @tablename
--  and colstat <> 1



DECLARE @vchrWhere varchar(1000)
set @vchrWhere = ''


OPEN curFields
FETCH NEXT FROM curFields INTO @fieldName, @fieldType, @fieldTypeVB,@paramName, @intColstat
WHILE @@FETCH_STATUS = 0
BEGIN
 --build a where clause
 if @intColStat = 1 begin
  set @vchrWhere = @vchrWhere  + '        ' + @fieldName + ' = ' + @paramName+ char(13)
 end
 --build a param list
 set @paramList = @paramList + char(13) + @paramName + ' ' + @fieldType + ','


 --build the 'set' list
 set @vchrSetList = @vchrSetList + char(13) + space(8) + @fieldName + ' = ' + @paramName + ','



 --build a VB param list
 set @paramListVB = @paramListVB + ', ' + 'ByVal ' + @fieldName + ' As ' + @fieldTypeVB  


 --build a sqlhelper param list
 set @paramListHelper = @paramListHelper + ', _ & ' + char(13) + space(16) + 'New SqlParameter("' + @paramName + '", ' + @fieldName + ')'
--
/*
 sp_update 'cn20100'


    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "_4P_CN20100_UPD" )


    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "_4P_CN20100_UPD" _ & 
                New SqlParameter("@DEX_ROW_ID", DEX_ROW_ID) )


    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "_4P_CN20100_UPD",  _ & 
                New SqlParameter("@CUSTNMBR", CUSTNMBR),  _ &
                New SqlParameter("@CPRCSTNM", CPRCSTNM),  _ &
                New SqlParameter("@DEX_ROW_ID", DEX_ROW_ID) )



*/


 FETCH NEXT FROM curFields INTO @fieldName, @fieldType,@fieldTypeVB, @paramName, @intColstat



END
CLOSE curFields
DEALLOCATE curFields


set @paramListVB = stuff(@paramListVB,1,2,'')


--remove the first and last char from the list
set @vchrSetList = stuff(@vchrSetList,1,1,'')
set @vchrSetList = left(@vchrSetList, len(@vchrSetList)-1)


--remove the last char from the list
set @paramList = left(@paramList, len(@paramList)-1)



print @paramlist
print ''
print 'AS'
print ''
print 'UPDATE ' + @tablename + ' SET'
print @vchrSetList


--print '    FROM ' + @tablename 
PRINT '    WHERE '
print @vchrWhere
-- sp_update 'cn20100'


print ''
print 'GO'
print ''
print 'grant all on ' + @vchrProcName + ' to public'
print ''
print ''
print ''
print ''
print 'Public Sub ' + @tableName + '_UPD(' + @paramListVB + ') '
print '    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "' + @vchrProcName + '"' + @paramListHelper + ' )'
print 'End Sub'



go


grant all on sp_update to public



 

    Posted Jan 10 2008, 05:33 PM by Steve Gray
    [Edit Tags]

  • sp_class

    This SQL script will create the code needed to put into a VB class to use with the Microsoft Data Application blocks.

    Two other scripts are needed in this code

    f_4P_columnType
    f_4P_columnTypeVB

    -- =============================================
-- v 1.5
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = N'sp_class' 
    AND    type = 'P')
    DROP PROCEDURE sp_class
GO



CREATE PROCEDURE sp_class 
-- sp_class '_4P_groups_INS'



@vchrProcName varchar(50)


 


AS
 
declare @output varchar(8000)
declare @fieldTypeVB varchar(50)
declare @vchrVBFieldName varchar(500)
declare @fieldType varchar(50)
declare @paramName varchar(255)
declare @paramList varchar(1000)
declare @paramListVB varchar(1000)
declare @paramListHelper varchar(1000)
declare @paramListHelper3 varchar(1000)
declare @vchrMethodName varchar(50)



set @output = ''



select
 @paramList = '',
 @paramListVB = '',
 @paramListHelper = '',
 @paramListHelper3 = '',
 @vchrMethodName = substring(@vchrProcName,5,99)



 



declare curFields CURSOR for
select p.name,
  dbo.f_4P_columnType (p.xtype,length, [xprec] , scale),
  dbo.f_4P_columnTypevb (p.xtype)
 from sysobjects o
  join syscolumns p on p.id = o.id
 where o.name = @vchrProcName


 


OPEN curFields
FETCH NEXT FROM curFields INTO @paramName, @fieldType, @fieldTypeVB--,@paramName
WHILE @@FETCH_STATUS = 0
BEGIN



 set @vchrVbFieldName = case when @paramName like <a href="mailto:'@vchr%'">'@vchr%'</a> then replace(@paramName,'@vchr','str') else replace(@paramName,'@','') end
 --build a param list



 --build a VB param list
 set @paramListVB = @paramListVB + ', ' + 'ByVal ' + @vchrVBFieldName + ' As ' + @fieldTypeVB  
 --build a sqlhelper param list
 set @paramListHelper = @paramListHelper + ', _ ' + char(13) + char(10) + space(10) + 'New SqlParameter("' + @paramName + '", ' + @vchrVBFieldName + ')'
 --build a sqlhelper3 param list
 set @paramListHelper3 = @paramListHelper3 + char(13) + char(10) + 
 space(10) + 'db.AddInParameter(dbCommand,"' + @paramName + '", DbType.String,' + @vchrVBFieldName + ')'


 FETCH NEXT FROM curFields INTO @paramName, @fieldType,@fieldTypeVB


END
CLOSE curFields
DEALLOCATE curFields



if @paramListVB > '' 
 set @paramListVB = stuff(@paramListVB,1,2,'')



print 'This procedure creates several different types of classes, choose the one that you need'
print ''
print 'For the Microsoft Data Access Blocks 3.0:'
print ''
print 'Sub ' + @vchrMethodName + '(' + @paramListVB + ') '
print '    Try'
print '        Dim db As Database = DatabaseFactory.CreateDatabase()'
print '        Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand("' + @vchrProcName + '")' + @paramListHelper3 
print ''
print '        db.ExecuteNonQuery(dbCommand)'
print '    Catch ex As Exception'
print '        Throw New Exception(ex.Message, ex.InnerException)'
print '    End Try'
print 'End Sub'
print ''
print 'For the Microsoft Data Access Blocks 2.0:'
print ''


print 'Public Sub ' + @vchrMethodName + '(' + @paramListVB + ') '
print '    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "' + @vchrProcName + '"' + @paramListHelper + ' )'
print 'End Sub'
print ' '
print 'Public Function ' + @vchrMethodName + '(' + @paramListVB + ') as sqldatareader'
print '    return SqlHelper.ExecuteReader(m_cs, CommandType.StoredProcedure, "' + @vchrProcName + '"' + @paramListHelper + ' )'
print 'End Sub'
print ''



go


grant exec on sp_class to public



 

    Posted Jan 08 2008, 02:20 PM by Steve Gray
    [Edit Tags]