Prev: Query for non unique record
Next: SQL Query needed
From: bad_clone on 23 Apr 2010 14:09
Hi,

I am inserting data into a table using xml bulk insert. One of the
field is of string type and contains data with leading and trailing
spaces. But on insert the spaces automatically trimmed off. Can you
please tell me how to avoid this trimming of spaces, as i have to
insert data using xml.

EXEC sp_xml_preparedocument @hDoc OUTPUT, @InsertLines

INSERT INTO tblLine WITH (TABLOCKX) ([Text]) SELECT [Text] FROM
OPENXML (@hDoc, '/Lines/Line', 1) WITH ([Text] nvarchar(max))

EXEC sp_xml_removedocument @hDoc

Thank you
Frank
From: Erland Sommarskog on 23 Apr 2010 18:18
bad_clone (bad_clone(a)hotmail.com) writes:
> I am inserting data into a table using xml bulk insert. One of the
> field is of string type and contains data with leading and trailing
> spaces. But on insert the spaces automatically trimmed off. Can you
> please tell me how to avoid this trimming of spaces, as i have to
> insert data using xml.
>
> EXEC sp_xml_preparedocument @hDoc OUTPUT, @InsertLines
>
> INSERT INTO tblLine WITH (TABLOCKX) ([Text]) SELECT [Text] FROM
> OPENXML (@hDoc, '/Lines/Line', 1) WITH ([Text] nvarchar(max))
>
> EXEC sp_xml_removedocument @hDoc

Can you supply a sample document?

Also, which version of SQL Server are you using?

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: bad_clone on 3 May 2010 15:51
On 23 avr, 18:18, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> bad_clone (bad_cl...(a)hotmail.com) writes:
> > I am inserting data into a table using xml bulk insert. One of the
> > field is of string type and contains data with leading and trailing
> > spaces. But on insert the spaces automatically trimmed off. Can you
> > please tell me how to avoid this trimming of spaces, as i have to
> > insert data using xml.
>
> > EXEC sp_xml_preparedocument @hDoc OUTPUT, @InsertLines
>
> > INSERT INTO tblLine WITH (TABLOCKX) ([Text]) SELECT [Text] FROM
> > OPENXML (@hDoc, '/Lines/Line', 1) WITH ([Text] nvarchar(max))
>
> > EXEC sp_xml_removedocument @hDoc
>
> Can you supply a sample document?

Using the SQL Server Profiler, here is the command executed with its
XML document:
exec p_mystoredproc @UpdateLines=N'<Lines><Line LineId="544011"
Text="This is a test " /></Lines>'



> Also, which version of SQL Server are you using?

I'm using SQL Server 2005 (9.00.3042.00)

From: Erland Sommarskog on 3 May 2010 17:49
bad_clone (bad_clone(a)hotmail.com) writes:
> Using the SQL Server Profiler, here is the command executed with its
> XML document:
> exec p_mystoredproc @UpdateLines=N'<Lines><Line LineId="544011"
> Text="This is a test " /></Lines>'
>
>
>
>> Also, which version of SQL Server are you using?
>
> I'm using SQL Server 2005 (9.00.3042.00)

Good! This means that you don't have to use OPENXML, but can use
the xml data type and the type methods. They are less kldugy to use -
no need for prepare/remove document, and they are more efficient. And
moreover, they retain the trailing space:

declare @x xml
select @x = N'<Lines><Line LineId="544011"
Text="This is a test " /></Lines>'
select LineId = T.c.value('@LineId', 'int'),
Text = '<' + T.c.value('@Text', 'nvarchar(MAX)') + '>'
from @x.nodes('/Lines/Line') AS T(c)

Crash cours:

nodes() produces a fragment per node in a one column table. The
syntax T(c) defines an alias for the table and gives a name to
the column.

value() then extracts a value from the node. You need the @ because
you extract an attribute.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Query for non unique record
Next: SQL Query needed