OPENXML Truncating Trailing spaces

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OPENXML Truncating Trailing spaces

Author

Topic

REDDY
Starting Member

43 Posts

Posted - 2006-10-04 : 13:30:11

When I extract xml data into a table using OPENXML ,it seems trailing spaces are being truncated,can any one help to stop the truncation.

Example:In the following example,I have a space for ContactName Field value =" Paul1 Henriot",But the result set i am getting is truncated value.

Thanks
Reddy

DECLARE @idoc int
-- create table #t1 ( c1 int,contactname varchar(20))

DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="123" ContactName=" Paul1 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>

</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.

insert into #t1
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID int,
ContactName varchar(20))
EXEC sp_xml_removedocument @idoc
select * from #t1
delete #t1

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2006-10-04 : 16:49:34

(1 row(s) affected)

c1 contactname
----------- --------------------
123 Paul1 Henriot

(1 row(s) affected)

(1 row(s) affected)

no issues.

With Regards
BSR

REDDY
Starting Member

43 Posts

Posted - 2006-10-04 : 17:40:34

Hi BSR
Thanks for your reply.

Did you able to get the contactname with trailing spaces as it was in original xml data ??

I am expecting to get contactname = " Paul1 Henriot" (Trailing space exists before P)

Check this
select Resultlen = len(ContactName), Expectedlen= len(' Paul1 Henriot'),* from #t1

Thanks
Reddy

Kristen
Test

22859 Posts

Posted - 2006-10-05 : 01:34:31

I didn't spot that in your XML originally, but I did look at it and then saw what you meant. I think if you refer to it as a leading space the rest of the crew will see the issue.

Also if you change

select * from #t1

to

SELECT '>' + ContactName + '<' from #t1

you can see whether the space is retained, or trimmed.

Kristen

REDDY
Starting Member

43 Posts

Posted - 2006-10-05 : 09:36:24

Kristen,
You are right,infact it was leading space,I have metioned it as trailing space by mistake.

In BTW I tried both leading and trailing spaces,both are being truncated after I extract the xml data into table.

Thanks for you correction.

Reddy

Kristen
Test

22859 Posts

Posted - 2006-10-05 : 13:26:04

Afraid I don't know why it does this, or whether you can work around it. There is a RAW mode of XML, but its a lot more work to use ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources