Re: specify criteria dynamically with OpenXML

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: RobKaratzas (RobKaratzas_at_discussions.microsoft.com)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 09:59:03 -0800

hi adam

yep, you're right...

but for some real goofy reasons, I need to process it in a loop. (Might be
running an array of process steps, etc.)

But here's how I've done this (just for testing...):

---------------------------------------------
DECLARE @hDoc int,@done int, @SQL nvarchar(4000),@parms decimal(10),@StepID
nchar(5), @IsActive nchar(1),@StepName nchar(255), @statementID nchar(255),
@statement nvarchar(4000),@xmlID int,@xmlStepName varchar(255),@xmlIsActive
varchar(1)
set @parms=1
set @statementID=1
set @xmlID=1
set @xmlStepName = 'Process Part'
set @xmlIsActive = 'Y'
set @done=0

EXEC sp_xml_preparedocument @hDoc OUTPUT,
      N'<ROOT>
         <Steps StepID="1" IsActive="Y" StepName="Process Part">
            <statements statementID="1"
               sql="select..."/>
            <statements statementID="2"
               sql="INSERT INTO PRT_PRT SELECT TOP 1 RefID, IsActive, MetID,
PartName, PartDesc, MAX(VersionNo) + 1 AS VersionNo FROM dbo.PRT_PRT WHERE
(PrtID = ?) GROUP BY RefID, IsActive, MetID, PartName, PartDesc"/>
         </Steps>
         <Steps StepID="2" IsActive="Y" StepName="Process Meta">
            <statements statementID="1"
               sql="select..."/>
            <statements statementID="2"
               sql="insert..."/>
         </Steps>
         <Steps StepID="3" IsActive="N" StepName="Process Post">
            <!--statements to be completed for job x by 3/1/2005...-->
         </Steps>
      </ROOT>'

WHILE @done=0
BEGIN

-- Use OPENXML to provide rowset consisting of customer data.
SET
@SQL='/ROOT/Steps[@StepName='+char(39)+@xmlStepName+char(39)+'][@IsActive='+char(39)+@xmlIsActive+char(39)+']/statements[@statementID='+char(39)+CONVERT(VARCHAR(2),@xmlID)+char(39)+']'
PRINT @SQL
SELECT
@StepID=StepID,@IsActive=IsActive,@StepName=StepName,@statementID=statementID,@statement=sql
FROM OPENXML(@hDoc, @SQL)
with (StepID nchar(5) '../@StepID', IsActive nchar(1)
'../@IsActive',StepName nchar(255) '../@StepName',
statementID nchar(255), sql nvarchar(4000))

print replicate('-',20)
print @StepID
print @IsActive
print @StepName
print @statementID
print @statement
print replicate('-',20)

If @xmlID = convert(int,@statementID)
BEGIN
        PRINT 'process statement: '+convert(varchar(2),@statementID)+'...'
        set @xmlID=@xmlID+1
END
ELSE
BEGIN
         set @done=1
END
END
-- set @sql=replace(@sql,'?',@parms)
-- Remove the internal representation of the XML document.

-- set @sql=replace(@sql,'?',@parms)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @hDoc
---------------------------------------------
"Adam Machanic" wrote:

> I think it would be quite a bit more efficient in this case to either insert
> all of the rows into a temp table and then loop over that, or write a cursor
> over the results of a single call to OPENXML. That way the XML would need
> to be processed only once.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
> "RobKaratzas" <RobKaratzas@discussions.microsoft.com> wrote in message
> news:6CC29806-BB59-417C-A629-22BD853871A8@microsoft.com...
> > got it, u need to do something like this (do the assignments prior to the
> > OPENXML):
> >
> > SET @SQL='/ROOT/Steps[@StepName=''Process
> > Part''][@IsActive=''Y'']/statements[@statementID=''2'']'
> > SELECT
> >
> @StepID=StepID,@IsActive=IsActive,@StepName=StepName,@statementID=statementI
> D,@statement=sql
> > FROM OPENXML(@hDoc, @SQL)
> > with (StepID nchar(5) '../@StepID', IsActive nchar(1)
> > '../@IsActive',StepName nchar(255) '../@StepName',
> > statementID nchar(255), sql nvarchar(4000))
> >
> > rob
> >
> >
> > "RobKaratzas" wrote:
> >
> > > Hi Folks
> > >
> > > I need to put the following OPENXML into a while loop (to process each
> row
> > > individually), but don't seem to be able to apply a filter using
> variables.
> > >
> > > Don't seem to be able to find the docs/syntax to use variables here:
> > >
> > > [@StepName=''Process
> Part''][@IsActive=''Y'']/statements[@statementID=''1'']'
> > >
> > > I need to loop on @StepName and @statementID (IsActive will stay
> constant).
> > >
> > > Please see this code below, which you can run in Query Analyzer:
> > >
> > >
> > >
> > > DECLARE @hDoc int,@SQL nvarchar(4000),@parms decimal(10),@StepID
> nchar(5),
> > > @IsActive nchar(1),@StepName nchar(255), @statementID nchar(255),
> @statement
> > > nvarchar(4000)
> > > set @parms=1
> > > EXEC sp_xml_preparedocument @hDoc OUTPUT,
> > > N'<ROOT>
> > > <Steps StepID="1" IsActive="Y" StepName="Process Part">
> > > <statements statementID="1"
> > > sql="select..."/>
> > > <statements statementID="2"
> > > sql="INSERT INTO PRT_PRT SELECT TOP 1 RefID,
> > > IsActive,
> > > MetID,
> > > PartName,
> > > PartDesc,
> > > MAX(VersionNo) + 1 AS VersionNo
> > > FROM dbo.PRT_PRT
> > > WHERE (PrtID = ?)
> > > GROUP BY
> > > RefID, IsActive, MetID, PartName, PartDesc"/>
> > > </Steps>
> > > <Steps StepID="2" IsActive="Y" StepName="Process Meta">
> > > <statements statementID="1"
> > > sql="select..."/>
> > > <statements statementID="2"
> > > sql="insert..."/>
> > > </Steps>
> > > <Steps StepID="3" IsActive="N" StepName="Process Post">
> > > <!--statements to be completed for job x by 3/1/2005...-->
> > > </Steps>
> > > </ROOT>'
> > > -- Use OPENXML to provide rowset consisting of customer data.
> > > SELECT
> > >
> @StepID=StepID,@IsActive=IsActive,@StepName=StepName,@statementID=statementI
> D,@statement=sql
> > > FROM OPENXML(@hDoc, N'/ROOT/Steps[@StepName=''Process
> > > Part''][@IsActive=''Y'']/statements[@statementID=''1'']')
> > > with (StepID nchar(5) '../@StepID', IsActive nchar(1)
> > > '../@IsActive',StepName nchar(255) '../@StepName',
> > > statementID nchar(255), sql nvarchar(4000))
> > >
> > > print replicate('-',20)
> > > print @StepID
> > > print @IsActive
> > > print @StepName
> > > print @statementID
> > > print @statement
> > > print replicate('-',20)
> > >
> > > -- Remove the internal representation of the XML document.
> > > EXEC sp_xml_removedocument @hDoc
> > >
> > > Thanks Rob
>
>
>

Relevant Pages

  • Re: Newbie: VB-ADO help
    ... I Do have some SQL knowledge and because the calculations are a bit ... the first loop ... are too numerous for memory storage. ... I do not want to deal with SQL and the like. ...
    (microsoft.public.vb.general.discussion)
  • Re: Straight SQL always put perform PL/SQL?
    ... If you cannot do it in a single SQL Statement, ... end loop; ... analyze table test1 compute statistics; ...
    (comp.databases.oracle.server)
  • Invalid cursor state on second $sth->execute
    ... I have written that a script that opens a cursor on a table in a DB2 database then processes each record in the result set inside a loop. ... In the course of the loop I prepare and execute another SQL statement to get a maximum value from the same table for a given condition. ... I then decided that to speed up the script I would prepare this lookup SQL outside the loop using placeholders, so I can just re-execute the same SQL with the different values plugged in each time. ...
    (perl.beginners)
  • Re: DTS Transform Loop
    ... the data from the RS to the array, then manipulate the array (display data, ... because it keeps the trips to the SQL Server down. ... > Loop over the files and insert 1 at a time. ... >> Then this goes into the Active-X loop and the ExcelLink is loaded into ...
    (microsoft.public.sqlserver.dts)
  • Re: Newbie: VB-ADO help
    ... I create a table to store some calculation results because they ... 'END LOOP 1 and LOOP 2 ... -Now I have to loop again for a second set of calculations (dependent on X ... I do not want to deal with SQL and the like. ...
    (microsoft.public.vb.general.discussion)