|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-15 : 10:27:29
|
| rj writes "I am trying to perform a query using 'FOR XML EXPLICIT'. Everything works until I try to filter the data by adding a where clause using a set list passed to the query. (where pole_num IN (@polenum)). The pole_num field is varchar. It works if there is one item in the list, but returns nothing if there is more than one item.Here is some sample data./* POLE TABLE */CREATE TABLE [POLE] ( [BLOCK] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [POLE_NUM] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PLOT_NUM] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [HEIGHT] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CLASS] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [JOINTUSE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [POLE_NO] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MAPNAME] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [X] [float] NULL , [Y] [float] NULL ) ON [PRIMARY]GO/* POLE INSPECTION TABLE */CREATE TABLE [pole_inspection] ( [pid] [int] IDENTITY (1, 1) NOT NULL , [ispdate] [smalldatetime] NULL , [polenbr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [location] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [manufacturer] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [actdate] [numeric](4, 0) NULL , [estdate] [numeric](4, 0) NULL , [orig_trt] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [o_gl_circ] [numeric](3, 0) NULL , [e_gl_circ] [numeric](3, 0) NULL , [height] [numeric](3, 0) NULL , [class] [numeric](2, 0) NULL , [last_trt] [numeric](4, 0) NULL , [treat] [bit] NULL , [reject] [bit] NULL , [s_and_b] [bit] NULL , [report] [bit] NULL , [stencil] [bit] NULL , [fumigant] [bit] NULL , [int_trt] [bit] NULL , [gwr] [bit] NULL , [vine_ctrl] [bit] NULL , [gms] [bit] NULL , [comments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [dept] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [initial] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_poles] PRIMARY KEY CLUSTERED ( [pid] ) ON [PRIMARY] ) ON [PRIMARY]GO/* POLE DATA */INSERT INTO POLE values('POLE', '262317013', NULL, NULL, NULL, NULL, '12', '262', 1465836.9177379999, 664901.11265499995)INSERT INTO POLE values('LPOLE', '359208653', NULL, NULL, NULL, NULL, '41616', '359-21', 1417183.669061, 610105.24759499996)/* POLE INSPECTION DATA */INSERT INTO POLE_Inspection values('2001-01-13 00:00:00', '12', '334-39', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 'PAD MOUNT - OK')INSERT INTO POLE_Inspection values('2001-02-02 00:00:00', '12', '384-64', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, 'PAD MOUNT - OK')INSERT INTO POLE_Inspection values('2001-01-22 00:00:00', '41616', '359-20', 'MER', 87, NULL, 'P', 26, 26, 30, 6, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL)/* SQLXML TEMPLATE */<?xml version="1.0" ?><root xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:header> <sql:param name="polenum"></sql:param> </sql:header> <sql:query>select 1 as tag, NULL as parent, p.[block] as [pole!1!block], p.[pole_num] as [pole!1!pole_num], p.[plot_num] as [pole!1!plot_num], p.[height] as [pole!1!height], p.[class] as [pole!1!class], p.[jointuse] as [pole!1!jointuse], p.[pole_no] as [pole!1!pole_no], p.[mapname] as [pole!1!mapname], p.[x] as [pole!1!x], p.[y] as [pole!1!y], NULL as [poleinspection!2!ispdate]from [mapguide]..[vUniquePoles |
|
using sqlxml and set lists in a where clause 