SQL Server 200 XML Parsing Cursor Problem

[radcaesar]

Hi,
I am reading a xml file using T-SQL. I want the xml data to use in openxml. So i was using cursor here.

Here i am concatenating the xml data into a variable called @FileContents. When i print this variable content inside the WHILE FetchStatus of CURSOR, its printing fine. If i print after the cursor execution s over, it doen't shows anything and its blank.

Where am i missing ?


DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
DECLARE @idoc int

CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileContents = ''
SET @FileName = 'd:\CCCLoginConfig.xml'
SET @ExecCmd = 'type ' + @FileName

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd

DECLARE @AccountID varchar(8000)
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR select ThisLine from #tempxml
OPEN @getAccountID
FETCH NEXT FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileContents = @FileContents + cast(@AccountID as varchar(8000))
FETCH NEXT FROM @getAccountID INTO @AccountID
--print @FileContents
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

select @FileContents
EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents

SELECT * FROM OPENXML (@idoc,'/DTSConfiguration/ConfiguredValue ',2) WITH (ConfiguredValue varchar(10))
-- ContactName varchar(20))

DROP TABLE #tempXML

[Jim Doherty]

Hi,
I am reading a xml file using T-SQL. I want the xml data to use in openxml. So i was using cursor here.

Here i am concatenating the xml data into a variable called @FileContents. When i print this variable content inside the WHILE FetchStatus of CURSOR, its printing fine. If i print after the cursor execution s over, it doen't shows anything and its blank.

Where am i missing ?


DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
DECLARE @idoc int

CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileContents = ''
SET @FileName = 'd:\CCCLoginConfig.xml'
SET @ExecCmd = 'type ' + @FileName

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd

DECLARE @AccountID varchar(8000)
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR select ThisLine from #tempxml
OPEN @getAccountID
FETCH NEXT FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileContents = @FileContents + cast(@AccountID as varchar(8000))
FETCH NEXT FROM @getAccountID INTO @AccountID
--print @FileContents
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

select @FileContents
EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents

SELECT * FROM OPENXML (@idoc,'/DTSConfiguration/ConfiguredValue ',2) WITH (ConfiguredValue varchar(10))
-- ContactName varchar(20))

DROP TABLE #tempXML

Having replicated this my end I suspect you're encountering a concatenation of a null that is cancelling out the variable assignment.

I have placed the 'print' after the deallocation at my end and it works with this amendment

 
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileContents = ISNULL(@FileContents + cast(@AccountID as varchar(8000)),@FileContents)
FETCH NEXT FROM @getAccountID INTO @AccountID
--print len(@FileContents)
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
print @FileContents
 

Regards

Jim :)

[iburyak]

If Jim is right and you have null at the end then do following:

SET @FileContents = @FileContents + isnull(cast(@AccountID as varchar(8000)),'')

Good Luck.

[iburyak]

I hate cursors.

Maybe you want to try reading whole file at once.
I don't have time changing this code for you (going on vacation for couple of weeks) but you can try inserting into a variable instead of a table or read after initial insert into a variable.

CREATE TABLE SomeTable (XML varchar(8000) null)
 
BULK INSERT [SomeTable]
FROM '\\nyinfrep001\ftproot$\Import\test.xml'

Good Luck.

[Jim Doherty]

If Jim is right and you have null at the end then do following:

SET @FileContents = @FileContents + isnull(cast(@AccountID as varchar(8000)),'')

Good Luck.

If I am right Iburyak is there something I have missed? in the While loop? does not the isnull function return the current non null value of Filecontent 'as is' without the necessity for appending a zero length string as well?

Regards

Jim :)

[iburyak]

If I am right Iburyak is there something I have missed? in the While loop? does not the isnull function return the current non null value of Filecontent 'as is' without the necessity for appending a zero length string as well?

Regards

Jim :)

I am not sure it is a question or a statement.
If you add null to a string you get null, if you replace that null with an empty string you get your string as is. It should work the way you wrote it and the way i wrote it but it is a mater of programmers preference. I don't think it is necessary appending null to a string and then checking if it is not what you wanted to get and then replacing it with original string. I prefer not to make unnecessary manipulations and check what I add to the string and if it is null not to do it.
If you ask me I wouldn't write this while loop in a first place. See my second suggestion which in my opinion is more efficient.


Nice talking to you Jim.... :)
By the way where are you from?

[Jim Doherty]

I am not sure it is a question or a statement.
If you add null to a string you get null, if you replace that null with an empty string you get your string as is. It should work the way you wrote it and the way i wrote it but it is a mater of programmers preference. I don't think it is necessary appending null to a string and then checking if it is not what you wanted to get and then replacing it with original string. I prefer not to make unnecessary manipulations and check what I add to the string and if it is null not to do it.
If you ask me I wouldn't write this while loop in a first place. See my second suggestion which in my opinion is more efficient.


Nice talking to you Jim.... :)
By the way where are you from?

I gave up making statements eons ago in my teens LOL! so no it was a question to a fellow programmer on a question of specific mindset consideratons thats all :) you've answered it 'both' isnulls perform in exactly the same way.
the cursor useage wouldnt be my preference either I have to say but there you go thats differences for you :)) The poster seems to be interested as to 'why' it wasnt working so I dealt with it 'I think' in the context of his question as opposed to benchmarking performance differences.

Nice talking to you too and I am in the UK, the midlands, in the leafy suburbs of Derbyshire

Jim :)

[iburyak]

I am in NY. My company has a branch in UK and I work with Londoners every day... :)
English is not my first language and I possibly misunderstood your question.
So good you've got what you were looking for from my answer... :)
Sometimes I do what you did just respond to specific problem and not go into changing whole approach to solution. But this case seem more advanced and interesting I would say. It made me thinking out loud I guess.

In real life I receive whole XML file as a parameter to a stored procedure. So in my case front end takes care of reading XML file and passing it's content to the stored procedure. But I thought it is interesting to read file itself from a stored procedure.
But the problem here is that you always have to remember that server's C or D drive is not the same as your computer's C or D drive it could be access rights violations and lots more.

Nice talking to you.
Irina.

[radcaesar]

See my code, since i was using the sp_xml_preparedocument, it will not acept its @FileContent Parameter with a WhiteSpace at end.

The error thrown was

'The error description is 'Required white space was missing.'

Any workaround for this ?

EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents

[iburyak]

sp_xml_preparedocument is very strict and requires well formed XML only.

If you have XML like this:

<?xml version="1.0"?> 
<downloads> 
        <products> 
 
        </products> 
</downloads> 

The answer is there should be a space before the ?

<?xml version="1.0" ?> 
<downloads> 
        <products> 
 
        </products> 
</downloads>

[Jim Doherty]

See my code, since i was using the sp_xml_preparedocument, it will not acept its @FileContent Parameter with a WhiteSpace at end.

The error thrown was

'The error description is 'Required white space was missing.'

Any workaround for this ?

EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents

Given we don't have your xml file to go on referenced by this bit

 
SELECT * FROM OPENXML (@idoc,'/DTSConfiguration/ConfiguredValue ',2) WITH (ConfiguredValue varchar(10))
-- ContactName varchar(20))
 

Then we are left to guess and are obliged to subsitute with something that is well formed in order to replicate your issue so.....

This simple bunch of text if saved in the root of C as MyCustomers.xml using a text editor here:

 
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
     <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
     <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
     <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>
 

Will produce the output you require when this code is run in Query analyser (Your Select * FROM OPENXML statement is commented out obviously to reference the example shown the attribute/element centric mapping flag is set to 1. You need to compare yours against this because this works within the constructs of the program flow)

 
DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
DECLARE @idoc int
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileContents = ''
SET @FileName = 'c:\MyCustomers.xml'
SET @ExecCmd = 'type ' + @FileName
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
DECLARE @AccountID varchar(8000)
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR select ThisLine from #tempXML
OPEN @getAccountID
FETCH NEXT FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileContents = isnull(@FileContents + cast(@AccountID as varchar(8000)),@FileContents)
FETCH NEXT FROM @getAccountID INTO @AccountID
--print @FileContents
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
--print @FileContents
select @FileContents
EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents
--SELECT * FROM OPENXML (@idoc,'/DTSConfiguration/ConfiguredValue ',2) WITH (ConfiguredValue varchar(10))
-- ContactName varchar(20))
 SELECT * FROM OPENXML (@idoc,'/ROOT/Customer',1) WITH (CustomerID varchar(10),ContactName varchar(20))
DROP TABLE #tempXML

Hope this helps you

Regards

Jim :)

[radcaesar]

Thanks JIM,

I Had solved this immediately after 5 mins when i posted this reply. But i had forgot to revert back with you guys. Sorry.

Thanks.

[Jim Doherty]

Thanks JIM,

I Had solved this immediately after 5 mins when i posted this reply. But i had forgot to revert back with you guys. Sorry.

Thanks.

Thanks for posting anyway glad you sorted it

Jim