Need to sum columns: queries

Showing posts with label queries. Show all posts

Friday, March 30, 2012

Network error when running a query

Running long queries (mostly DBCC commands) on SQL Server 2000 SP3 I receive
an error very often
"[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead())
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation."
How can I fix it?Hi,
Do you have SQL Server on Windows 2003?
If you do, try removing Named Pipes from Enabled Network Libraries in Server
Network Utility.
--
Danijel Novak
"Elena" <Elena@.discussions.microsoft.com> wrote in message
news:07702D19-01BA-4A62-9F1B-CC758C1C4460@.microsoft.com...
> Running long queries (mostly DBCC commands) on SQL Server 2000 SP3 I
> receive
> an error very often
> "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (WrapperRead())
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation."
> How can I fix it?
>|||Thanks for reply, Danijel. Yes, SQL Server 2000 is running on Windows Server
2003 machine. But how will removing Named Piies influence application
performance? Do I need to stop and restart production server after
reconfiguration?
Elena
"Danijel Novak" wrote:
> Hi,
> Do you have SQL Server on Windows 2003?
> If you do, try removing Named Pipes from Enabled Network Libraries in Server
> Network Utility.
> --
> Danijel Novak
>
> "Elena" <Elena@.discussions.microsoft.com> wrote in message
> news:07702D19-01BA-4A62-9F1B-CC758C1C4460@.microsoft.com...
> > Running long queries (mostly DBCC commands) on SQL Server 2000 SP3 I
> > receive
> > an error very often
> > "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> > (WrapperRead())
> > Server: Msg 11, Level 16, State 1, Line 0
> > General network error. Check your network documentation."
> > How can I fix it?
> >
>
>|||Hi,
removing Named Pipes should not influence application performance. Just keep
TCP/IP in there.
I'm not sure about restarting the service but I think you don't have to
restart SQL Server for that.
--
Danijel Novak
"Elena" <Elena@.discussions.microsoft.com> wrote in message
news:0CD3D9ED-DA33-4D7B-BDD3-C3C486158A68@.microsoft.com...
> Thanks for reply, Danijel. Yes, SQL Server 2000 is running on Windows
> Server
> 2003 machine. But how will removing Named Piies influence application
> performance? Do I need to stop and restart production server after
> reconfiguration?
> Elena
> "Danijel Novak" wrote:
>> Hi,
>> Do you have SQL Server on Windows 2003?
>> If you do, try removing Named Pipes from Enabled Network Libraries in
>> Server
>> Network Utility.
>> --
>> Danijel Novak
>>
>> "Elena" <Elena@.discussions.microsoft.com> wrote in message
>> news:07702D19-01BA-4A62-9F1B-CC758C1C4460@.microsoft.com...
>> > Running long queries (mostly DBCC commands) on SQL Server 2000 SP3 I
>> > receive
>> > an error very often
>> > "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
>> > (WrapperRead())
>> > Server: Msg 11, Level 16, State 1, Line 0
>> > General network error. Check your network documentation."
>> > How can I fix it?
>> >
>>

Network error when running a query

Monday, March 19, 2012

Nested queries difficulty

Hi!
I'm a SQL beginner struggling with some SQL textbook examples, and i'm stuck at one exercise. :eek:
Can anyone give me a hint??

Ok, here it goes:
I'm trying to simulate a airport booking system, where CUSTOMER makes a RESERVATION to a FLIGHT. The FLIGHT have a ROUTE, a AIRCRAFT, and some CABINSTAFF. Plus some other minor attribures..

The question i'm trying to answer now is "how many seats are there left on all flights having a specific route?". I can get how many seats the airplanes have with this:

SELECt SEATS
FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
WHERE FL.FNUMBER = R.FLIGHTNUMBER
AND FL.AIRCRAFT = AC.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND "DATE" = '2005-03-01';

It will return:
100
200
(there are two flights that match the selected route and date, one airplane have 100 seats, the other one 200).

I then find out how many customers that are booked on those flights:

SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
AND RN.FNUMBER = R.FLIGHTNUMBER
AND F.FNUMBER = RN.FNUMBER
AND F.AIRCRAFT = A.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND FDATE = '2005-03-01'
GROUP BY RN.RESERVATION_NO

It will correctly return:
1
2

Now i want to substract the second query from the first to get the number of seats left in those flights:

SELECT FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
AND RN.FNUMBER = R.FLIGHTNUMBER
AND F.FNUMBER = RN.FNUMBER
AND F.AIRCRAFT = A.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND FDATE = '2005-03-01'
GROUP BY RN.RESERVATION_NO)
FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
WHERE FL.FNUMBER = R.FLIGHTNUMBER
AND FL.AIRCRAFT = AC.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND "DATE" = '2005-03-01';

But that does'nt work since you can't substract a set, only a single row, right?? So how do i fix this??
The answer should be:
219
98

Thanks!Without getting into the details of your query, you need to correlate the subquery to the main query something like this:

SELECT FL.FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, ...
WHERE ...
AND F.FLIGHTNUMBER = FL.FLIGHTNUMBER)
FROM FLIGHT FL, ...;

nested queries

i got a question of how to use a result you achieve from a query in table
form and use the result to find other stuff form the other tables
for example
SELECT Omim_No
FROM av
WHERE Description LIKE '%LIVER%'
ORDER BY Omim_No ASC
SELECT Omim_No
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
ORDER BY Omim_No ASC
SELECT Omim_No
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
SELECT Omim_No
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
ORDER BY Omim_No ASC
SELECT Omim_No
FROM tx
WHERE Omim_Text LIKE '%LIVER%'
SELECT subsnp_id,pop_id,allele_id
FROM AlleleFreqBySsPop
WHERE source LIKE '%LIVER%'
Instead of seraching for the word liver in the last table i would like to
search from the result i gotten from the first five table is that possible?Store the results from the first few queries in a temporary table. A slight
modification would be needed:
create table #<temp table name>
(
Omim_No <datatype>
,TextValue ntext --?
)
insert #<temp table name>
(
Omim_No
,TextValue
)
SELECT Omim_No as Omim_No
,Description as TextValue
FROM av
WHERE Description LIKE '%LIVER%'
union all
SELECT Omim_No
,CS_Description
FROM cs
WHERE CS_Description LIKE '%LIVER%'
OR CS_DATA LIKE '%LIVER%'
union all
SELECT Omim_No
,Omim_Titles
FROM ti
WHERE Omim_Titles LIKE '%LIVER%'
union all
SELECT Omim_No
,Omim_Alt_Titles
FROM ti_alt_title
WHERE Omim_Alt_Titles LIKE '%LIVER%'
union all
SELECT Omim_No
,Omim_Text
FROM tx
WHERE Omim_Text LIKE '%LIVER%'
Also consider using full-text search, it will certainly perform batter that
the LIKE operator.
ML
http://milambda.blogspot.com/|||i have a data base to search from i search the data column omim no and i
have to link to the other table in th esame database through the omim no i
gotten through some of the other tables.
"ML" wrote:

> Store the results from the first few queries in a temporary table. A sligh
t
> modification would be needed:
> create table #<temp table name>
> (
> Omim_No <datatype>
> ,TextValue ntext --?
> )
> insert #<temp table name>
> (
> Omim_No
> ,TextValue
> )
> SELECT Omim_No as Omim_No
> ,Description as TextValue
> FROM av
> WHERE Description LIKE '%LIVER%'
> union all
> SELECT Omim_No
> ,CS_Description
> FROM cs
> WHERE CS_Description LIKE '%LIVER%'
> OR CS_DATA LIKE '%LIVER%'
> union all
> SELECT Omim_No
> ,Omim_Titles
> FROM ti
> WHERE Omim_Titles LIKE '%LIVER%'
> union all
> SELECT Omim_No
> ,Omim_Alt_Titles
> FROM ti_alt_title
> WHERE Omim_Alt_Titles LIKE '%LIVER%'
> union all
> SELECT Omim_No
> ,Omim_Text
> FROM tx
> WHERE Omim_Text LIKE '%LIVER%'
>
> Also consider using full-text search, it will certainly perform batter tha
t
> the LIKE operator.
> ML
> --
> http://milambda.blogspot.com/|||The temporary table in my previous post stores the results of your queries
and can be joined on the Omim_No column to any other table where this column
is used.
What is the problem?
ML
http://milambda.blogspot.com/

Monday, March 12, 2012

Nested Full Text Queries

Can someone help me or provide a workaround? The object is to select all
articles which are not references to other articles. The problem I am
having is when I try to get the ReferenceCount (which is the count of
articles which reference this article) it is throwing a 42000 (Error 170:
Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
nested full text query. I have tried successfully using LIKE '%' +
a.[Message-ID] + '%' which returns correctly just slow. By the way the The
References field is a varchar(500).
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
John,
I'm not exactly sure what you're trying to achieve with this query, but it
is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
what you may want to do is an INNER JOIN between the FTS query using
CONTAINSTABLE and your table tblarticles. For example, the syntax of the
rowset-based CONTAINSTABLE is:
SELECT select_list
FROM table AS FT_TBL INNER JOIN
CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
-- and an example using a Pubs database table:
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
"greene")' ) AS KEY_TBL
WHERE
FT_TBL.au_id = KEY_TBL.[KEY]
/*-- returns:
au_lname au_fname RANK
-- -- --
Greene Morningstar 80
Green Marjorie 80
Ringer Anne 64
Ringer Albert 64
(4 row(s) affected)
*/
I'd recommend altering your query to use the above CONTAINSTABLE or
FREETEXTTABLE syntax.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:oU_gc.23991$6m4.934106@.twister.southeast.rr.c om...
> Can someone help me or provide a workaround? The object is to select all
> articles which are not references to other articles. The problem I am
> having is when I try to get the ReferenceCount (which is the count of
> articles which reference this article) it is throwing a 42000 (Error 170:
> Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
> nested full text query. I have tried successfully using LIKE '%' +
> a.[Message-ID] + '%' which returns correctly just slow. By the way the
The
> References field is a varchar(500).
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS
ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
>
>
|||Thanks for replying john,
Unless I am overlooking something and please correct me if I am, you simply
provided an example of how to do a standard full text search. I do not have
a problem doing that. I thought I explained what im trying to achieve very
well.
Again, This is what im trying to do but its to slow:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
This is what I have tried:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%' "
for "CONTAINS([References], a.[Message-ID])"
"John Kane" <jt-kane@.comcast.net> wrote in message
news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
> John,
> I'm not exactly sure what you're trying to achieve with this query, but it
> is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
> what you may want to do is an INNER JOIN between the FTS query using
> CONTAINSTABLE and your table tblarticles. For example, the syntax of the
> rowset-based CONTAINSTABLE is:
> SELECT select_list
> FROM table AS FT_TBL INNER JOIN
> CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
> ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
> -- and an example using a Pubs database table:
> SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
> FROM authors as FT_TBL,
> CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
> "greene")' ) AS KEY_TBL
> WHERE
> FT_TBL.au_id = KEY_TBL.[KEY]
> /*-- returns:
> au_lname au_fname RANK
> -- -- --
> Greene Morningstar 80
> Green Marjorie 80
> Ringer Anne 64
> Ringer Albert 64
> (4 row(s) affected)
> */
> I'd recommend altering your query to use the above CONTAINSTABLE or
> FREETEXTTABLE syntax.
> Regards,
> John
|||You're welcome, John,
I've given this some more thought and have done some testing with the
authors table in the Pubs database I do not have the table schema for your
tblarticles table. First of all, the syntax you are using for the CONTAINS
predicate is incorrect, and assuming you are referencing a variable in the
search clause, your query should be re-written as follows:
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT [ID], [References], [From], [Date], [Subject],
(SELECT Count(ID) FROM tblarticles WHERE CONTAINS ([References],
@.SearchStr)) AS ReferenceCount
FROM tblarticles a
WHERE [GroupID] = @.GroupID AND [References]=''
ORDER BY [Date] Desc;
Additionally, I tested the following alternative using CONTAINSTABLE with
and INNER JOIN on the a SELECT * from CONTAINSTABLE. However, this solution
does not work with count(*) or count(au_id) for the join condition.
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL INNER JOIN
(SELECT * FROM
CONTAINSTABLE(authors,au_lname, @.SearchStr)) AS KEY_TBL
ON FT_TBL.au_id = KEY_TBL.[KEY]
/* -- returns:
au_lname au_fname RANK
--- -- --
Green Marjorie 80
White Johnson 80
(2 row(s) affected)
*/
Finally, I altered the initial query and use the assignment symbol "=" for
the SELECT * from authors where contains() and this did the trick.
declare @.SearchStr varchar(8000), @.State char(2)
SET @.SearchStr = '"green" or "white"'
SET @.State = 'CA'
SELECT au_id, au_lname, au_fname, SearchCount = (SELECT count(*) FROM
authors where CONTAINS(au_lname, @.SearchStr))
FROM authors
WHERE state = @.State and contract = 0
ORDER BY au_lname DESC
/*-- returns:
au_id au_lname au_fname
SearchCount
-- --- -- --
724-08-9931 Stringer Dirk 2
893-72-1158 McBadden Heather 2
(2 row(s) affected)
*/
You should alter your query to match the above and confirm that this works
for your tblarticles table.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:qu8hc.44033$yv.952732@.twister.southeast.rr.co m...
> Thanks for replying john,
> Unless I am overlooking something and please correct me if I am, you
simply
> provided an example of how to do a standard full text search. I do not
have
> a problem doing that. I thought I explained what im trying to achieve
very
> well.
> Again, This is what im trying to do but its to slow:
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
> ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
> This is what I have tried:
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS
ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
> Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%'
"[vbcol=seagreen]
> for "CONTAINS([References], a.[Message-ID])"
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
it[vbcol=seagreen]
*think*
>
|||Again thanks John,
The problem I am having is that where you are using a predefined (or passed)
variable @.SearchStr, I am needing this to come from the outer query. This
is why you see my variable labeled as a.[Message-ID]. The "a" is a
reference to the outer SELECT statements [Message-ID] field.
My Table looks like this:
CREATE TABLE [dbo].[tblArticles] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[FROM] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE] [datetime] NOT NULL ,
[Subject] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Message-ID] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Path] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[Article] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[References] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GroupID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And for the record this gets the desired results, its just slow and does not
make use of full text.
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount FROM tblarticles a WHERE [GroupID] = @.GroupID AND
[References]='' ORDER BY [Date] Desc;
Which again brings me back to the original question of nesting full text
queries.
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], '%' + a.[Message-ID] + '%')) AS
ReferenceCount FROM tblarticles a WHERE [GroupID] = @.GroupID AND
[References]='' ORDER BY [Date] Desc;

> --SNIP --
>

nested FOR XML queries / with namespaces

I want to assign the result of a FOR XML query containing nested queries to an xml variable. The problem is, the namespace declarations propagate down to every nested element generated in the query.

DECLARE @.x xml
BEGIN
WITH XMLNAMESPACES('one' AS ns1, 'two' AS ns2)
SELECT @.x = (
SELECT -1 "@.id",
'invalid' "@.status",
(SELECT 'false' "@.flag",
'etc' "@.comment"
FOR XML PATH ('ns2:inner'), TYPE)
FOR XML PATH('ns1:0uter'), TYPE);
SELECT @.x
END;

This is the XML that is generated:

<ns1:0uter xmlns:ns2="two" xmlns:ns1="one" id="-1" status="invalid">
<ns2:inner xmlns:ns2="two" xmlns:ns1="one" flag="false" comment="etc" />
</ns1:0uter>

Is there an alternate way to declare a namespace in a nested query (in this case, move the namespace declaration for 'two AS ns2' out of the outer query into the nested select)? If not, then are there alternative ways to remove this extraneous stuff? This is a simple example, but real-world instances involving several levels of nesting these declarations are too much.

I have the same question. I simply need to add

xmlns="urn:xxxx.yyyyy.zzzzz"

in the header of the XML only.

I review the MDSN details: http://msdn2.microsoft.com/en-us/library/ms177400.aspx but don't see how to accomplish this simple thing.

I can't even fake it out:

SELECT

'en-US' AS "MessageLanguage",

'2007-05-08T18:13:51.0Z' AS "IssueDate",

'urn:xxxx.yyyyy.zzzzz' AS "@.xmlns",

The select returns error:

'xmlns' is invalid in XML tag name in FOR XML PATH, or when WITH XMLNAMESPACES is used with FOR XML.

|||

I have the same problem with this query:

Code Snippet

declare @.category_name as varchar(512);
set @.category_name = 'Bolt';

with xmlnamespaces ('http://services.ihs.com/schemas/structured_content' as sc)

select id as [@.sc:id], 'ADD' as [@.sc:action], cat_id as [@.sc:categoryId],

(select attribute_id as [@.sc:attributeId], value as [data()]

from Item_Detail (nolock)

where item_id = Item.id

for xml path('sc:VALUE'), type)

from Item (nolock)

where cat_id = (select id from Category (nolock) where name = @.category_name)

for xml path('sc:ITEM'), root('sc:ITEMS')
;

Each of the 22 million nested sc:VALUE result elements unnecessarily rebind the namespace prefix:

Code Snippet

<sc:ITEMS xmlns:sc="http://services.ihs.com/schemas/structured_content">

<sc:ITEM sc:id="ABE9639D-3C7B-4E66-BF32-00001A294577" sc:action="ADD" sc:categoryId="68541B13-9C60-4D90-B694-68C28F346832">

<sc:VALUE xmlns:sc="http://services.ihs.com/schemas/structured_content" sc:attributeId="E9BD274C-34C2-44A1-8F54-6E5218C71A9A">STEEL ALLOY</sc:VALUE>

...

nested FOR XML queries / with namespaces

I want to assign the result of a FOR XML query containing nested queries to an xml variable. The problem is, the namespace declarations propagate down to every nested element generated in the query.

This is the XML that is generated:

<ns1:0uter xmlns:ns2="two" xmlns:ns1="one" id="-1" status="invalid">
<ns2:inner xmlns:ns2="two" xmlns:ns1="one" flag="false" comment="etc" />
</ns1:0uter>

I have the same question. I simply need to add

xmlns="urn:xxxx.yyyyy.zzzzz"

in the header of the XML only.

I review the MDSN details: http://msdn2.microsoft.com/en-us/library/ms177400.aspx but don't see how to accomplish this simple thing.

I can't even fake it out:

SELECT

'en-US' AS "MessageLanguage",

'2007-05-08T18:13:51.0Z' AS "IssueDate",

'urn:xxxx.yyyyy.zzzzz' AS "@.xmlns",

The select returns error:

'xmlns' is invalid in XML tag name in FOR XML PATH, or when WITH XMLNAMESPACES is used with FOR XML.

|||

I have the same problem with this query:

Code Snippet

declare @.category_name as varchar(512);
set @.category_name = 'Bolt';

with xmlnamespaces ('http://services.ihs.com/schemas/structured_content' as sc)

select id as [@.sc:id], 'ADD' as [@.sc:action], cat_id as [@.sc:categoryId],

(select attribute_id as [@.sc:attributeId], value as [data()]

from Item_Detail (nolock)

where item_id = Item.id

for xml path('sc:VALUE'), type)

from Item (nolock)

where cat_id = (select id from Category (nolock) where name = @.category_name)

for xml path('sc:ITEM'), root('sc:ITEMS')
;

Each of the 22 million nested sc:VALUE result elements unnecessarily rebind the namespace prefix:

Code Snippet

<sc:ITEMS xmlns:sc="http://services.ihs.com/schemas/structured_content">

<sc:ITEM sc:id="ABE9639D-3C7B-4E66-BF32-00001A294577" sc:action="ADD" sc:categoryId="68541B13-9C60-4D90-B694-68C28F346832">

<sc:VALUE xmlns:sc="http://services.ihs.com/schemas/structured_content" sc:attributeId="E9BD274C-34C2-44A1-8F54-6E5218C71A9A">STEEL ALLOY</sc:VALUE>

...

nested FOR XML queries / with namespaces

I want to assign the result of a FOR XML query containing nested queries to an xml variable. The problem is, the namespace declarations propagate down to every nested element generated in the query.

This is the XML that is generated:

<ns1:0uter xmlns:ns2="two" xmlns:ns1="one" id="-1" status="invalid">
<ns2:inner xmlns:ns2="two" xmlns:ns1="one" flag="false" comment="etc" />
</ns1:0uter>

I have the same question. I simply need to add

xmlns="urn:xxxx.yyyyy.zzzzz"

in the header of the XML only.

I review the MDSN details: http://msdn2.microsoft.com/en-us/library/ms177400.aspx but don't see how to accomplish this simple thing.

I can't even fake it out:

SELECT

'en-US' AS "MessageLanguage",

'2007-05-08T18:13:51.0Z' AS "IssueDate",

'urn:xxxx.yyyyy.zzzzz' AS "@.xmlns",

The select returns error:

'xmlns' is invalid in XML tag name in FOR XML PATH, or when WITH XMLNAMESPACES is used with FOR XML.

|||

I have the same problem with this query:

Code Snippet

declare @.category_name as varchar(512);
set @.category_name = 'Bolt';

with xmlnamespaces ('http://services.ihs.com/schemas/structured_content' as sc)

select id as [@.sc:id], 'ADD' as [@.sc:action], cat_id as [@.sc:categoryId],

(select attribute_id as [@.sc:attributeId], value as [data()]

from Item_Detail (nolock)

where item_id = Item.id

for xml path('sc:VALUE'), type)

from Item (nolock)

where cat_id = (select id from Category (nolock) where name = @.category_name)

for xml path('sc:ITEM'), root('sc:ITEMS')
;

Each of the 22 million nested sc:VALUE result elements unnecessarily rebind the namespace prefix:

Code Snippet

<sc:ITEMS xmlns:sc="http://services.ihs.com/schemas/structured_content">

<sc:ITEM sc:id="ABE9639D-3C7B-4E66-BF32-00001A294577" sc:action="ADD" sc:categoryId="68541B13-9C60-4D90-B694-68C28F346832">

<sc:VALUE xmlns:sc="http://services.ihs.com/schemas/structured_content" sc:attributeId="E9BD274C-34C2-44A1-8F54-6E5218C71A9A">STEEL ALLOY</sc:VALUE>

...

Friday, March 30, 2012

Network error when running a query

Network error when running a query

Network error when running a query

Monday, March 19, 2012

Nested queries difficulty

nested queries

Monday, March 12, 2012

Nested Full Text Queries

nested FOR XML queries / with namespaces

nested FOR XML queries / with namespaces

nested FOR XML queries / with namespaces

Need to sum columns

Blog Archive

About Me