How to verify tags in non-xml

[timscronin]

I have an INQ table which has a date field, orderid and a request type which is actually a big xml string. the data looks like this

<NewOrder><SiteID>CJC</SiteID><patID>458887</patID><LName>Cronin</LName><FName>tim</FName><EntryID>{7B1A4946-CEC8-4F23-AE89-5C70A6A0F9B2}</NewOrder>

Why I need to do is verify all the tags I need are there. In this case there should be 5. Since the data is in a sql server table, should I try to parse through it, or is there something on the xml side I should try. This is sql 2000

 

[harebrain]

Parsing it yourself would be re-inventing the wheel. I'd wrap the output in a root tag, create a simple DTD (your needs are simple) and use IE to validate the XML document. You can find help for these operations in faq426-6460.

 

[mrdenny]

I would use openxml to parse the XML string.

declare @string as varchar(1000)
declare @hDoc as int
declare @SiteID varchar(5),
	@patID int,
	@LName varchar(25),
	@FName varchar(25),
	@EntryID uniqueidentifier
set @string = '<NewOrder><SiteID>CJC</SiteID><patID>458887</patID><LName>Cronin</LName><FName>tim</FName><EntryID>{7B1A4946-CEC8-4F23-AE89-5C70A6A0F9B2}</EntryID></NewOrder>'

exec sp_xml_preparedocument @hdoc OUTPUT, @string

select @SiteID=SiteID, @patID=PatID,@LName=LName, @FName=FName, @EntryID=EntryID
from OPENXML (@hDoc, N'/NewOrder')
WITH (SiteID varchar(5) './SiteID/text()',
	patID int './patID/text()',
	LName varchar(25) './LName/text()',
	FName varchar(25) './FName/text()',
	EntryID uniqueidentifier './EntryID/text()')

exec sp_xml_removedocument @hdoc

if @SiteID is null or @patID is null or @LName is null or @FName is null or @EntryID is null
	raiserror ('There was an error in one of the fields.', 16, 1)

Just replace the manually setting of the variable @string with a DDL operation.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[timscronin]

Thanks, exactly what I was looking for

 

[timscronin]

One more question. It works great if a tag is missing. What if they just botch the xml say with a beginning tag, that is missing the end tag

 

[timscronin]

Tried this, but it returns a system error message

declare @error int
exec sp_xml_removedocument @hdoc
SELECT @Error = @@Error
IF @Error <> 0 BEGIN
print 'to'
end

 

[mrdenny]

It appears that if there is a missing tag it does in the sp_xml_preparedocument. This being SQL 2000 there isn't really any way to catch it and continue on with error handeling do to the level of the error message that is being caught.

You'll have to handle this in what ever code is calling the code or if a SQL job the job will fail and have to be responded to manually. If it's a job on failure you could have a cleanup script that pulls the offending record out, maybe based on a status table that you are update within the above code sending a message to the DBAs and the job then continues on.

Without knowing more about the job flow I'm just guessing here.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[timscronin]

Actually it is a job that calls this sp for processing, you are right under 2005 much easier to trap