[varchar] (100) and empty space

Hello

I am creating the following table

CREATE TABLE [dbo].[myT] (
[name] [varchar] (100) NULL
)

if I do

INSERT INTO myT (name) VALUES ('A')

i get in the table a column with

A ...

the 100 char place is full even if there is a data with only 1 char

how is it possible to avoid it ?
I want 100 char maximum but not full with nothing

thank you for helpingWhat do you get when you run this?:

select len([Name]), '[' + [Name] + ']' from myT

Name is a reserved word, and so it is not a good label for a column, but I don't think this would cause the problem you are seeing.|||the 100 char place is full even if there is a data with only 1 charHow do you know?

Could this be a display issue of whatever client program you use to display the data?
Only CHAR columns are padded to the full length not VARCHAR columns|||I know it when I fill a formula with datas I am getting 99 empty spaces

blindman it is an exemple i have no column named [name]

if i run select datalength(name) from myT
i am getting 200 2 times (100)|||Strange I just ran all the scripts above & everything looks good to me

I get 1 from select datalength(name) from myT

anselme

take a deep breath reboot and start running these scripts again

If this does'nt work

Reply here and

stop using the word Char as in the 100 char place is full even if there is a data with only 1 char

explain what query editor you are using

explain what database you are using

run the script exactly as blindman suggests select len([Name]), '[' + [Name] + ']' from myT and tell us exactly the output

GW|||blindman it is an exemple i have no column named [name]You have some sort of typo, and if you expect any more help on this you need to post the actual code so we don't waste more of our time.|||I know it when I fill a formula with datas I am getting 99 empty spacesSQL Server does not have "formulas" to be "filled" (whatever that should mean).
What exactly are you doing?|||i agree with Gwilliy everything looks cool for me too....
varchar will only occupy the required number of space...
however if still problems persist you can always use LTRIM and RTRIM to get rid of the remaining whitespaces

so your query will be something like
select LTRIM(RTRIM([name])) from myT

thats the most we can get you....

However the fact that the 100 varchar place is full still mystifies me|||However the fact that the 100 varchar place is full still mystifies me
I suspect this is a front end issue.
What is this "formula" thing he is filling in?

[SQL] One parameter and few values.

Hello!

I have tables "forum_topics" and "forum_categories":

forum_topics:
-topic_id
-topic_title
-topic_cat_id
-topic_user_id
-topic_date

forum_categories:
-cat_id
-cat_name

For example: I have 5 categories in table forum_categories and by this query I can display topics from 2nd and 3rd category only. So I'm using this query:
SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (2;3)

Now I want to parametrized this query by one parameter, so:

SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (@.param)

And I want to input this parameter lik in the query first - so @.param = "2;3"

But I have a problem to make this works, can someone explain me, how to solve this problem ? Thanks.

Czesc Grzesiek,

The quick and dirty way of doing it is to convert your SQL to a string and execute it later, this is useful when you have optional parameters. Your stor proc would look something like that:

Declare @.sqlas varchar(1000)SET @.sql ='SELECT topic_title FROM forum_topics WHERE topic_cat_id IN ('''+ @.param +''')'exec(@.sql)

|||

you want @.param to be comma separated value is that you are asking for ?

well you could do it in c# , vb.net or any language, could you tell me wht are you doing at the moment for that.

its simple just declare one string variable say str

string str = "";

if (str.length == 0)

str = some_value;

else

str += str +", " + some_other_value;

does that makes sense

regards,

satish

|||

Hey Tetris - you're from Poland maybe ? :)

I have:

ALTER PROCEDURE ShowTopics

@.param varchar
AS

Declare @.sql as varchar(max)

SET @.sql='SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN ('''+@.param+'''))'
exec(@.sql)RETURN

All as you wrote. But when I write as @.param "1,2,3" I'm getting only topics (records) where cat_id is "1". If I choose "3,2,1" I will get only from 3rd category.

|||

I wanna add only that this query:

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN (1,2,3)

Returns me all topics from category 1st,2nd and 3rd. So my question is simply - how can I put (and how declare, or I don't know) in place 1,2,3 a parameter @.categories, and how make it works ?

|||

Oh God ...

My mistake was at the begin of stored procedure. There was:

ALTER PROCEDURE ShowTopics
@.param varchar()

Should be
ALTER PROCEDURE ShowTopics
@.param varchar(100)

(for example)

No it works !!!! Thanks Tetris ! Big thanks !

|||I'm glad you got it to work :) Yeh I'm polish but I live in Canada.|||

So I have one more question.

I have 5 checkboxes:
Checkbox1 (Category 1st),Checkbox2 (Category 2nd),Checkbox3 (Category 3rd),Checkbox4 (Category 4th),Checkbox5 (Category 5th).

And as cookie "Forum_kategorie" I have value = "1,3,5".

So I have topics from category 1,3 and 5. Do you know how can I easily checked checkbox 1,3 and 5 and unchecked second and fourth ?

Default value of "@.param" in this parametrized query above will be 1,2,3,4,5. And at first enter to site all five checkboxes will be checked. I will unchecked second and fourth and click the button, which will add/change cookie "Forum_kategorie" to value "1,3,5" and reload site. After that I will see topics from 1,3,5 category, but I have in some way to define which checkbox is un/checked.

Any ideas ?

Dzi?ki za pomoc :-) / Thanks for help :-)

|||If you are re-loading the same page, as long as you have EnableViewState = True for each checkbox, it should remember what it's last selection was when the site re-loads (on post back) Is this the case?|||

As you have a small rabge of values, take a look at using bitwise operators.

Your check box values would be bases on multiples of two: 1,2,4,8,16.

The value of the parameter that you pass into your stored procedure would be the sum of the values of the check boxes.

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id & @.Categories = forum_kategorie.forum_kat_id)

[effeciency] specific result values

Hi.

I had no idea what to name the topic so I hope this is ok.

I feel like I am losing it, even though I am still learning SQL Server - I dont use it much in terms of technical/complex queries and so on but I do use SQL on a regular (almost) basis. I am all about making sure it is secure and effecient and performance and so on - just to give a bit of background about myself.

Say for instance I have an ASP.NET website, which I have developed a discussion board from scratch using SQL Server to store all the information.

Say for instance, we have a topic, and that topic will be "locked".

Would I be correct in saying that on such a table, "Topics", there should be a field which is known as "ActiveStatus" or "IsLocked", and depending on if the thread is locked or not, that value is set on the table field? Am I correct in saying this?

If not - then what is the correct way of stating/retrieving if the topic is locked, in other words, what is the best way to have control over the thread so you can lock the topic?

I cannot think of another way but to store this one value in SQL on this topic table, along with other fields. I would then get the result by calling the procedure from ASP.NET and accordingly, set an image button on the webpage to either "locked" or "post reply"

Sorry for being silly, but I want to confirm if I am about to do this correctly or not. I want to make sure I am doing the best practice all the time, and enjoy doing it naturally. What is the best design/decision on such a scenario/situation?

Many thanks for your input, I greatly appreciate it! :-)

You're not being silly at all.

It makes sense to add a column such as IsLocked or TopicLocked to your Topics table, preferably with a bit datatype, since the value will be either 1 for locked or 0 for unlocked. Then your web page can query that value and allow access to the topic or not.

Good luck with your application.

|||

Many thanks and yes I was also thinking of using the bit datatype

Thank-you! I just want to make sure im not being silly and missing something obvious :)

have a great day!

[DB2] Update Field in Table A to a Field in Table B

I am trying to change the values in Field2 of table A to the values in Field2 of table B wherever the tables have the same value for Field1.

This works in Microsoft Access, but I can't figure out how to make it work in DB2.

UPDATE A INNER JOIN B ON A.Field1 = B.Field1 SET A.Field2 = [B].[Field2];

Any ideas?I know this works in Oracle, but not tested in DB2...

UPDATE a
SET a.field2 = NVL( ( SELECT b.field2
FROM b
WHERE b.field1 = a.field1), a.field2);|||try this (untested; i don't have DB2, but i know it allows scalar subqueries in the UPDATE statement) --update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )|||Thanks, I just replaced NVL with Coalesce and it worked fine. I greatly appreciate the help.|||Rudy,

Just so you know... I tried your variation in an attempt to find a solution for d_lynch... problem I found was that:

update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )

...works for the fields that have a match, however, if there is no match, whatever was in A.FIELD2 is now replaced with a NULL.|||thanks, joe, i understand that

i wouldn't update A.Field2 with itself, though -- could be lotsa useless log activity

i'd use a WHERE clause to ensure that only those rows which had a match are actually updated|||Cool... not really into correcting other people's code, but had tried it so I thought I'd mention the results. BTW, always appreciate your answers to questions... very well thought out.

@xml.value against XML data with namespaces

i have some XML, that i am using .value to get the values from, when the
namespa e is included in the XML(as below ) it doesn't work (everything come
s
as NULL), however if i manually take out toe xmlns part of the xml, it works
fine. i'm just wondering how i can change my .value code to work with the
namespace..
declare @.myxml XML
set @.myxml =
'<CodingNoticesP6P6B
xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
xmlns:gt="http://www.govtalk.gov.uk/CM/core"
xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
<EmployerRef>961/1791574</EmployerRef>
<Name>
<Title>AA</Title>
<Forename>AA</Forename>
<Surname>AA</Surname>
</Name>
<NINO>AA000000A</NINO>
<WorksNumber>WN001</WorksNumber>
<EffectiveDate>2005-09-01Z</EffectiveDate>
<CodingUpdate>
<TaxCode>10A</TaxCode>
<TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
<TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
</CodingUpdate>
</CodingNoticesP6P6B>'
--doesn't work when namespace is in, need to make the query work with the
namespace somehow
select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
P6_IssueDate,
@.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
P6_EmployerRef,
@.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
P6_Title,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
as P6_ForeName,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
as P6_ForeName_2,
@.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
P6_Surname,
@.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
@.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
as P6_WorksNumber,
@.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
P6_EffectiveDate,
@.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
P6_FormType,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
'varchar(5)' ) as P6_TaxCode,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.W1Month1Indicator
)[1]' , 'char(1)' ) as P6_W1Month1Indicator,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
, 'numeric(9,2)' ) as P6_TotalPreviousPay,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1
]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
'numeric(9,2)' ) as P6_TotalPreviousTax,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1
]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
thanksKarl,
The query processor only has several builtin namespace/prefix mappings
builtin. You need to supply these additional mappings since each xml instanc
e
that the query runs against may have their own namespace/prefix mappings.
There are two way to create this mapping. One is to use the "declare" clause
in a value or query method. For example:
select @.myxml.value('declare namespace ns1 =
"http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2";
(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as P6_IssueDate
But, since you have many value methods, you can also use the WITH
XMLNAMESPACES clause before the SELECT statement. This defines
namespace/prefix mappings for all value and query methods within a single
select statement. See BOL for me details.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as ns1)
select @.myxml.value('(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate
You will need to place the correct prefix in front of each element name
within your xpath expressions. In the example you gave, all the elements use
the default namespace.
Note: You should place a semicolon after the statement that precedes the
WITH clause. In your case, the set statement should have ; at the end.
Regards,
Galex Yen
"Karl Prosser" wrote:

> i have some XML, that i am using .value to get the values from, when the
> namespa e is included in the XML(as below ) it doesn't work (everything co
mes
> as NULL), however if i manually take out toe xmlns part of the xml, it wor
ks
> fine. i'm just wondering how i can change my .value code to work with the
> namespace..
> declare @.myxml XML
> set @.myxml =
> '<CodingNoticesP6P6B
> xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
> xmlns:gt="http://www.govtalk.gov.uk/CM/core"
> xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
> TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
> <EmployerRef>961/1791574</EmployerRef>
> <Name>
> <Title>AA</Title>
> <Forename>AA</Forename>
> <Surname>AA</Surname>
> </Name>
> <NINO>AA000000A</NINO>
> <WorksNumber>WN001</WorksNumber>
> <EffectiveDate>2005-09-01Z</EffectiveDate>
> <CodingUpdate>
> <TaxCode>10A</TaxCode>
> <TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
> <TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
> </CodingUpdate>
> </CodingNoticesP6P6B>'
> --doesn't work when namespace is in, need to make the query work with the
> namespace somehow
> select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) a
s
> P6_IssueDate,
> @.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) A
S
> P6_EmployerRef,
> @.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
> P6_Title,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
> as P6_ForeName,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
> as P6_ForeName_2,
> @.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' )
as
> P6_Surname,
> @.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
> @.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
> as P6_WorksNumber,
> @.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
> P6_EffectiveDate,
> @.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
> P6_FormType,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
> 'varchar(5)' ) as P6_TaxCode,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.W1Month1Indicat
or)[1]' , 'char(1)' ) as P6_W1Month1Indicator,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]
'
> , 'numeric(9,2)' ) as P6_TotalPreviousPay,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)
[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
> 'numeric(9,2)' ) as P6_TotalPreviousTax,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)
[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
> thanks|||thank you very much, i appreciate your help and feedback
here is an example of what i am doing now.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
'varchar(14)' ) AS P6_EmployerRef,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
'varchar(4)' ) as P6_Title,
i noticed that in multilevel parts i have to put p6 at every level (i.e
codingbase, name and title, as shown above for it to work.. Is this Normal?
shouldn't it be able to know from the first one? Of course i can get it
working with the p6 and that makes me happy. jUst curious about the details
thanks again|||Karl,
The reason the prefix must be specified over and over again is simply an
artifact of xml+namespaces. An xml document may have elements/attributes fro
m
multiple namespaces and within those namespace there could be name
collisions. An element name is not really just the name part, it is also the
namespace it is defined in.
That being said, in your case, since only one namespace is being used, you
can define the default namespace (no prefix) throughout your query. This can
be done like this:
WITH XMLNAMESPACES (DEFAULT 'http://your.uri')
SELECT xmlcol.value('/foo[1]', 'int')
In this case '/foo' really means {http://your.uri}:foo. Note: this isn't
really xml syntax, just an illustration.
Regards,
Galex Yen
"Karl Prosser" wrote:

> thank you very much, i appreciate your help and feedback
> here is an example of what i am doing now.
> WITH XMLNAMESPACES
> ('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
> select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime'
)
> as P6_IssueDate,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) a
s
> P6_SequenceNumber,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
> 'varchar(14)' ) AS P6_EmployerRef,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
> 'varchar(4)' ) as P6_Title,
> i noticed that in multilevel parts i have to put p6 at every level (i.e
> codingbase, name and title, as shown above for it to work.. Is this Normal
?
> shouldn't it be able to know from the first one? Of course i can get it
> working with the p6 and that makes me happy. jUst curious about the detail
s
> thanks again

@xml.value against XML data with namespaces

i have some XML, that i am using .value to get the values from, when the
namespa e is included in the XML(as below ) it doesn't work (everything comes
as NULL), however if i manually take out toe xmlns part of the xml, it works
fine. i'm just wondering how i can change my .value code to work with the
namespace..
declare @.myxml XML
set @.myxml =
'<CodingNoticesP6P6B
xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
xmlns:gt="http://www.govtalk.gov.uk/CM/core"
xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
<EmployerRef>961/1791574</EmployerRef>
<Name>
<Title>AA</Title>
<Forename>AA</Forename>
<Surname>AA</Surname>
</Name>
<NINO>AA000000A</NINO>
<WorksNumber>WN001</WorksNumber>
<EffectiveDate>2005-09-01Z</EffectiveDate>
<CodingUpdate>
<TaxCode>10A</TaxCode>
<TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
<TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
</CodingUpdate>
</CodingNoticesP6P6B>'
--doesn't work when namespace is in, need to make the query work with the
namespace somehow
select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
P6_IssueDate,
@.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
P6_EmployerRef,
@.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
P6_Title,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
as P6_ForeName,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
as P6_ForeName_2,
@.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
P6_Surname,
@.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
@.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
as P6_WorksNumber,
@.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
P6_EffectiveDate,
@.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
P6_FormType,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
'varchar(5)' ) as P6_TaxCode,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.Week1Month1Indicator)[1]' , 'char(1)' ) as P6_Week1Month1Indicator,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
, 'numeric(9,2)' ) as P6_TotalPreviousPay,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
'numeric(9,2)' ) as P6_TotalPreviousTax,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
thanks
Karl,
The query processor only has several builtin namespace/prefix mappings
builtin. You need to supply these additional mappings since each xml instance
that the query runs against may have their own namespace/prefix mappings.
There are two way to create this mapping. One is to use the "declare" clause
in a value or query method. For example:
select @.myxml.value('declare namespace ns1 =
"http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2";
(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as P6_IssueDate
But, since you have many value methods, you can also use the WITH
XMLNAMESPACES clause before the SELECT statement. This defines
namespace/prefix mappings for all value and query methods within a single
select statement. See BOL for me details.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as ns1)
select @.myxml.value('(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate
You will need to place the correct prefix in front of each element name
within your xpath expressions. In the example you gave, all the elements use
the default namespace.
Note: You should place a semicolon after the statement that precedes the
WITH clause. In your case, the set statement should have ; at the end.
Regards,
Galex Yen
"Karl Prosser" wrote:

> i have some XML, that i am using .value to get the values from, when the
> namespa e is included in the XML(as below ) it doesn't work (everything comes
> as NULL), however if i manually take out toe xmlns part of the xml, it works
> fine. i'm just wondering how i can change my .value code to work with the
> namespace..
> declare @.myxml XML
> set @.myxml =
> '<CodingNoticesP6P6B
> xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
> xmlns:gt="http://www.govtalk.gov.uk/CM/core"
> xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
> TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
> <EmployerRef>961/1791574</EmployerRef>
> <Name>
> <Title>AA</Title>
> <Forename>AA</Forename>
> <Surname>AA</Surname>
> </Name>
> <NINO>AA000000A</NINO>
> <WorksNumber>WN001</WorksNumber>
> <EffectiveDate>2005-09-01Z</EffectiveDate>
> <CodingUpdate>
> <TaxCode>10A</TaxCode>
> <TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
> <TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
> </CodingUpdate>
> </CodingNoticesP6P6B>'
> --doesn't work when namespace is in, need to make the query work with the
> namespace somehow
> select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
> P6_IssueDate,
> @.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
> P6_EmployerRef,
> @.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
> P6_Title,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
> as P6_ForeName,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
> as P6_ForeName_2,
> @.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
> P6_Surname,
> @.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
> @.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
> as P6_WorksNumber,
> @.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
> P6_EffectiveDate,
> @.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
> P6_FormType,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
> 'varchar(5)' ) as P6_TaxCode,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.Week1Month1Indicator)[1]' , 'char(1)' ) as P6_Week1Month1Indicator,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
> , 'numeric(9,2)' ) as P6_TotalPreviousPay,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
> 'numeric(9,2)' ) as P6_TotalPreviousTax,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
> thanks
|||thank you very much, i appreciate your help and feedback
here is an example of what i am doing now.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
'varchar(14)' ) AS P6_EmployerRef,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
'varchar(4)' ) as P6_Title,
i noticed that in multilevel parts i have to put p6 at every level (i.e
codingbase, name and title, as shown above for it to work.. Is this Normal?
shouldn't it be able to know from the first one? Of course i can get it
working with the p6 and that makes me happy. jUst curious about the details
thanks again
|||Karl,
The reason the prefix must be specified over and over again is simply an
artifact of xml+namespaces. An xml document may have elements/attributes from
multiple namespaces and within those namespace there could be name
collisions. An element name is not really just the name part, it is also the
namespace it is defined in.
That being said, in your case, since only one namespace is being used, you
can define the default namespace (no prefix) throughout your query. This can
be done like this:
WITH XMLNAMESPACES (DEFAULT 'http://your.uri')
SELECT xmlcol.value('/foo[1]', 'int')
In this case '/foo' really means {http://your.uri}:foo. Note: this isn't
really xml syntax, just an illustration.
Regards,
Galex Yen
"Karl Prosser" wrote:

> thank you very much, i appreciate your help and feedback
> here is an example of what i am doing now.
> WITH XMLNAMESPACES
> ('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
> select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
> as P6_IssueDate,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
> 'varchar(14)' ) AS P6_EmployerRef,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
> 'varchar(4)' ) as P6_Title,
> i noticed that in multilevel parts i have to put p6 at every level (i.e
> codingbase, name and title, as shown above for it to work.. Is this Normal?
> shouldn't it be able to know from the first one? Of course i can get it
> working with the p6 and that makes me happy. jUst curious about the details
> thanks again

@@IDLE Bug?

I have written a custom monitor, similar to sp_monitor that stores the
values of @.@.cpu_busy, @.@.io_busy, @.@.idle, etc... to a table.
I have observed that the value of @.@.idle prematurely wraps to a negative
number. Since this value is comprised of a 4 byte integer, one would expect
the value to wrap around to a negative value when it reaches 2,147,483,647.
However, my observations show that this value is wrapping at a value
somewhere just over 441150174 to a negative value somewhere just
under -95648727.
Does anyone have any insight as to why this system variable is prematurely
wrapping? What are the exact values that @.@.idle wraps at?Does anyone have insight into the SQL internals on how the @.@.idle value
rolls over?
"Don Ferguson" <don@.nospamplease> wrote in message
news:uyi0lQLhEHA.1724@.tk2msftngp13.phx.gbl...
> I have written a custom monitor, similar to sp_monitor that stores the
> values of @.@.cpu_busy, @.@.io_busy, @.@.idle, etc... to a table.
> I have observed that the value of @.@.idle prematurely wraps to a negative
> number. Since this value is comprised of a 4 byte integer, one would
expect
> the value to wrap around to a negative value when it reaches
2,147,483,647.
> However, my observations show that this value is wrapping at a value
> somewhere just over 441150174 to a negative value somewhere just
> under -95648727.
> Does anyone have any insight as to why this system variable is prematurely
> wrapping? What are the exact values that @.@.idle wraps at?
>