September 16, 2009 at 1:22 pm
I've got XML that specifies character values with varying numbers of leading spaces that need to be preserved. Unfortunately when I use OPENXML, it seems to always strip the leading blanks. I've tried using edge table, WITH table, and WITH schema specifications of various sorts, both encoding the values as attributes and elements without success. Am I missing something? Something obscure like the ":XML ON" directive that I stumbled across when trying to get my scripts to run in SQLCMD? 😉
Any help is greatly appreciated.
September 16, 2009 at 1:37 pm
Since you hide any sample data I had to come up with a short example...
I used XQuery instead of OPENXML since I don't know what version you're using either...
As you can see, the effect you're describing cannot be verified easily.
If you'd like us to see if there's anything we can help you with please read and follow the link in my signature on how to post sample data.
DECLARE @xml xml
SET @xml=' 1
2'
SELECT ':' + c.value('VAL1[1]','varchar(10)'),
c.value('VAL1[1]','varchar(10)')
FROM @xml.nodes('R') T(c)
/*result
(No column name)(No column name)
: 1 1
: 2 2*/
*/
September 16, 2009 at 1:56 pm
I just finished discovering the same, that by using nodes and value I can correctly get the values back with leading blanks. Apparently OPENXML does some automatic "cleansing" of the values that strips off blanks.
So instead of :
EXEC
sp_xml_preparedocument
@DocHandle
OUTPUT
,@XMLDoc
;
SELECT
*
FROM
OPENXML
(@DocHandle
,'/root/displayname_lima'
,1
)
WITH
dbo.displayname_translation
I have:
SELECT
table_name.value('@system_name[1]','nvarchar(512)') AS System_Name
,table_name.value('@translation[1]','nvarchar(512)') AS Translation
FROM
@XMLDoc.nodes('root/displayname_lima') AS DNT(table_name)
September 16, 2009 at 2:12 pm
October 8, 2009 at 1:05 am
How to convert the output of the query to string:
---------------------------------------------------------------------
/*create table #customers (
id int,
customer varchar(50)
)
insert into #customers values (1,'John')
insert into #customers values (2,'Lyss')
insert into #customers values (3,'Jack')
insert into #customers values (4,'David')
insert into #customers values (5,'Anne')
insert into #customers values (6,'Victoria')
go*/
SELECT 1 AS tag,
NULL AS parent,
id AS [customers!1!id],
customer AS [customers!2!customer]
FROM #customers AS customersa
UNION ALL
SELECT 2 AS tag,
1 AS parent,
id AS [customers!1!id],
customer AS [customers!2!customer]
FROM #customers AS customersb
ORDER BY [customers!2!customer] DESC,parent
FOR XML EXPLICIT, ROOT ('customers')
---------------------------------------------------------------------
How to convert a string to mind:
---------------------------------------------------------------------
<customers>
<customers id="6">
<customers customer="Victoria" />
</customers>
<customers id="2">
<customers customer="Lyss" />
</customers>
<customers id="1">
<customers customer="John" />
</customers>
<customers id="3">
<customers customer="Jack" />
</customers>
<customers id="4">
<customers customer="David" />
</customers>
<customers id="5">
<customers customer="Anne" />
</customers>
</customers>
October 9, 2009 at 7:47 am
Another reason not to use OPENXML.
See: Stop Using OPENXML
October 10, 2009 at 1:29 am
I watch necessarily your article
As long as that found here is such a decision :
------------------------------------------------------
create table #customers (
id int,
customer varchar(50)
)
insert into #customers values (1,'John')
insert into #customers values (2,'Lyss')
insert into #customers values (3,'Jack')
insert into #customers values (4,'David')
insert into #customers values (5,'Anne')
insert into #customers values (6,'Victoria')
go
DECLARE @xml XML
SELECT @xml =
(
SELECT
id AS "@id",
customer AS "customers/@customer"
FROM #customers
ORDER BY customer DESC--, parent
FOR XML PATH('customers'), ROOT ('customers')
)
SELECT
x.y.value('@id', 'INT') AS id,
x.y.value('customers[1]/@customer', 'VARCHAR(50)') AS customer
FROM @xml.nodes('customers/*') x(y)
October 10, 2009 at 1:33 am
I watch necessarily your article
As long as that found here is such a decision :
create table #customers (
id int,
customer varchar(50)
)
insert into #customers values (1,'John')
insert into #customers values (2,'Lyss')
insert into #customers values (3,'Jack')
insert into #customers values (4,'David')
insert into #customers values (5,'Anne')
insert into #customers values (6,'Victoria')
go
DECLARE @xml XML
SELECT @xml =
(
SELECT
id AS "@id",
customer AS "customers/@customer"
FROM #customers
ORDER BY customer DESC--, parent
FOR XML PATH('customers'), ROOT ('customers')
)
SELECT
x.y.value('@id', 'INT') AS id,
x.y.value('customers[1]/@customer', 'VARCHAR(50)') AS customer
FROM @xml.nodes('customers/*') x(y)
October 11, 2009 at 4:02 am
I believe first understand OPENXML
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply