Am I doin this right? SELECT / ExecuteNonQuery statement

I am working on a web service method that will return weather or not a page url is stored in the database but the ExecuteNonQuery keeps returning -1. I was just wondering if i was doing anything wrong or why the ExecuteNonQuery method does not return a value of 1 or more indicating that the pageurl exists in the database? I have tried using the SQLDataReader as well to no effect and I have verified that SELECT statement refers to valid table and field names. Any help or pointers would be appreciated. I'm still kind of a newb when it comes to db programming.

1 <WebMethod()> _
2Public Function IsPageStored(ByVal pageurlAs String)As Boolean
3 If String.IsNullOrEmpty(pageurl)Then Return False
45 Dim connAs New SqlConnection()
6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString
78Dim cmdAs String9 cmd ="SELECT [" & Constants.SourceFieldName &"] "10 cmd &="FROM [" & Constants.StoredCopyTableName &"] "11 cmd &="WHERE ([" & Constants.SourceFieldName &"] ='@.Source')"12 Dim C As New SqlCommand(cmd, conn)
13 C.Parameters.AddWithValue("@.Source", New SqlTypes.SqlString(pageurl))
14 C.Parameters.Item("@.Source").CompareInfo = SqlTypes.SqlCompareOptions.IgnoreCase
1516 conn.Open()
1718Dim existsAs Boolean =False19 exists = (C.ExecuteNonQuery > 0)
2021 conn.Close()
22 C.Dispose()
23 C =Nothing24 conn.Dispose()
25 conn =Nothing
2627 Return exists
28End Function29

ExecuteNonQuery:Runs theAdomdCommand without returning any results.

For the purpose I understand, you can use ExecuteReader or ExecuteScalar if you required only one column value is return.

I think you have to use. ExecuteScalar.

Please make sure you click the Answer button if this is true answer.

Regards.

|||

In addition to what the prior person mentioned, if all you want to know is that the record exists then query for a count. SELECT COUNT(primaryKey) FROM tblName WHERE .....<add all your where conditions here>. Then you can use ExecuteScalar - much more efficient.

|||

<WebMethod()> _
2 Public Function IsPageStored(ByVal pageurlAs String)As Boolean
3 If String.IsNullOrEmpty(pageurl)Then Return False
4
5 Dim connAs New SqlConnection()
6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString
7
8 Dim cmdAs String

cmd="IF EXISTS("
9 cmd &="SELECT [" & Constants.SourceFieldName &"] "
10 cmd &="FROM [" & Constants.StoredCopyTableName &"] "
11 cmd &="WHERE ([" & Constants.SourceFieldName &"] ='@.Source')"

cmd &= ") SET @.RETVAL=1 ELSE SET @.RETVAL=0"
12 Dim C As New SqlCommand(cmd, conn)
13 C.Parameters.AddWithValue("@.Source", New SqlTypes.SqlString(pageurl))
14 C.Parameters.Item("@.Source").CompareInfo = SqlTypes.SqlCompareOptions.IgnoreCase

c.Parameters.Add("@.RETVAL", SqlDbType.Int).Direction = ParameterDirection.Output

15
16 conn.Open()
17
18 Dim existsAs Boolean =False

c.ExecuteNonQuery
19 exists = c.Parameter("@.RETVAL").Value
20
21 conn.Close()
22 C.Dispose()
23 C =Nothing
24 conn.Dispose()
25 conn =Nothing
26
27 Return exists
28 End Function

Using the EXISTS allows SQL Server to stop retrieving records when it finds the very first record that matches, instead of having to find them all. This also should force SQL Server to optimize it's query plan.

I've also changed the code from the above examples to use an output parameter instead of returning a value as a resultset. This is a more efficient way to return a simple result as well.

|||

Thanks for the tips every one, they helped me greatly!

Always get null, can't return results

Please, Please, Please help. I tried a million different
ways of doing this and I can't get results, I always get
no results or NULL. I just want a node, just one node,
anything.
use telnform
SELECT bidData.query('
declare default element namespace=
"http://www.rfpexpress.com"
for $propCode in /X_NBTA05/PB/PROPCODE
return $propCode
')
FROM tblBidXMLData
USE [TelnForm]
GO
/****** Object: Table [dbo].[tblBidXMLData] Script
Date: 12/20/2004 11:48:10 ******/
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblBidXMLData](
[BidID] [int] NOT NULL,
[bidData] [xml] NULL,
[CorpID] [int] NOT NULL,
[CorpRFPNumber] [varchar](25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PropID] [int] NOT NULL,
[AutoSubmit] [bit] NOT NULL,
[ValidationErrors] [int] NULL,
[NamId] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS OFF
- <ns0:X_NBTA05 xmlns:ns0="http://www.rfpexpress.com"
xmlns:ns1="http://www.rfpexpress.com/Global">
- <ns0:PB>
<PROPCODE>34991</PROPCODE>
<PROPNAME>RFP Express Test Property - Product
Development</PROPNAME>
<PROPADD1>8989 Rio San Diego Dr11</PROPADD1>
<PROPADD2 />
<PROPCITY>Ixataville</PROPCITY>
<PROPSTATEPROV>CA</PROPSTATEPROV>
<PROPCOUNTRY>United States</PROPCOUNTRY>
<PROPPOSTCODE>99998</PROPPOSTCODE>
<MAINPHONECOUNTRY />
<MAINPHONECITY>619</MAINPHONECITY>
<MAINPHONE>4008840</MAINPHONE>
<MAINFAXCOUNTRY />
<MAINFAXCITY>619</MAINFAXCITY>
<MAINFAX>4008819</MAINFAX>
<UNITOFMEAS>MI</UNITOFMEAS>
<PROPURL>1</PROPURL>
<FULL_OR_LIM>L</FULL_OR_LIM>
<ES_STAY>Y</ES_STAY>
<MKT_TIER>L</MKT_TIER>
<AAADIAM_RATE>1</AAADIAM_RATE>
<MOBILSTAR_RATE>NA</MOBILSTAR_RATE>
<OHG_CLASS>Tourist</OHG_CLASS>
<YRBUILT>1952</YRBUILT>
<LASTREN>1234</LASTREN>
<NUMROOMS>555</NUMROOMS>
<NUMNOSMK>1</NUMNOSMK>
<NUMFLRS>45</NUMFLRS>
<MGMTCOMPANY>Gene Kaliman</MGMTCOMPANY>
<OWNERCOMPANY>Gene Kaliman</OWNERCOMPANY>
<MIN_OWNED_CERT>Y</MIN_OWNED_CERT>
<WMN_OWNED_CERT>N</WMN_OWNED_CERT>
<ADACOMPLY>N</ADACOMPLY>
<PROP_LOC>T</PROP_LOC>
<PROPOVERVIEW>Luxury hotel located in the heart of
downtown San Diego. Lovely view of the
harbor.</PROPOVERVIEW>
<AMEXCARD>Y</AMEXCARD>
<DINERSCARD>Y</DINERSCARD>
<DISCOVERCARD>Y</DISCOVERCARD>
<JCBCARD>Y</JCBCARD>
<MASTERCARD>Y</MASTERCARD>
<VISACARD>Y</VISACARD>
<AMADEUS_CHAINCODE>67</AMADEUS_CHAINCODE>
<AMADEUS_PROPCODE>555a</AMADEUS_PROPCODE>
<APOLLO_CHAINCODE>12</APOLLO_CHAINCODE>
<APOLLO_PROPCODE />
<SABRE_CHAINCODE>14</SABRE_CHAINCODE>
<SABRE_PROPCODE>15a</SABRE_PROPCODE>
<WORLDSPAN_CHAINCODE>56</WORLDSPAN_CHAINCODE>
<WORLDSPAN_PROPCODE />
<MASTERCHAINCODE>AS</MASTERCHAINCODE>
</ns0:PB>
- <ns0:CS>
<TEMPLATE_NAME>Consortia</TEMPLATE_NAME>
<OFFICENAME />
<OFFICEADD1 />
<RN_TARGET>N</RN_TARGET>
<RATE_CURR>USD</RATE_CURR>
<COMMISIONABLE>N</COMMISIONABLE>
<COMM_PCT_CLIENT />
<COMM_PCT_CONS />
<LOAD_GDS>Y</LOAD_GDS>
<WALK_HOTEL>N</WALK_HOTEL>
<WALK_TAX>N</WALK_TAX>
<WALK_TRANS>N</WALK_TRANS>
<WALK_CALL>N</WALK_CALL>
<CANC_POL>24hr</CANC_POL>
<EARLYCK_FEE>0</EARLYCK_FEE>
<EARLYCK_UOM>F</EARLYCK_UOM>
<EARLYCK_INCLUDE>N</EARLYCK_INCLUDE>
<LODGTX_FEE>13</LODGTX_FEE>
<LODGTX_UOM>P</LODGTX_UOM>
<LODGTX_INCLUDE>N</LODGTX_INCLUDE>
<STATETX_FEE>23</STATETX_FEE>
<STATETX_UOM>P</STATETX_UOM>
<STATETX_INCLUDE>Y</STATETX_INCLUDE>
<CITYTX_FEE>34</CITYTX_FEE>
<CITYTX_UOM>P</CITYTX_UOM>
<CITYTX_INCLUDE>N</CITYTX_INCLUDE>
<VATGST_FEE>45</VATGST_FEE>
<VATGST_UOM>P</VATGST_UOM>
<VATGST_INCLUDE>Y</VATGST_INCLUDE>
<SERVICE_FEE>56</SERVICE_FEE>
<SERVICE_UOM>P</SERVICE_UOM>
<SERVICE_INCLUDE>N</SERVICE_INCLUDE>
<OCC_FEE>76</OCC_FEE>
<OCC_UOM>P</OCC_UOM>
<OCC_INCLUDE>Y</OCC_INCLUDE>
<PARK_FEE>5</PARK_FEE>
<PARK_INCLUDE>N</PARK_INCLUDE>
<BREAK_FEE />
<BREAK_INCLUDE>N</BREAK_INCLUDE>
<FITNESS_FEE_ON>234</FITNESS_FEE_ON>
<FITNESS_INCLUDE_ON>N</FITNESS_INCLUDE_ON>
<FITNESS_FEE_OFF>0</FITNESS_FEE_OFF>
<FITNESS_INCLUDE_OFF>N</FITNESS_INCLUDE_OFF>
<HSIA_FEE>0</HSIA_FEE>
<HSIA_INCLUDE>N</HSIA_INCLUDE>
<WIRELESS_FEE>0</WIRELESS_FEE>
<WIRELESS_INCLUDE>N</WIRELESS_INCLUDE>
<COMP_INFAX>N</COMP_INFAX>
<COMP_OUTFAX>N</COMP_OUTFAX>
<AIRTRANS_FEE>0</AIRTRANS_FEE>
<AIRTRANS_INCLUDE>N</AIRTRANS_INCLUDE>
<OFFTRANS_FEE>0</OFFTRANS_FEE>
<OFFTRANS_INCLUDE>Y</OFFTRANS_INCLUDE>
<RATE_SUBS>N</RATE_SUBS>
<RATE_PERSO>N</RATE_PERSO>
<RATE_CONSU>N</RATE_CONSU>
<RATE_RETIR>N</RATE_RETIR>
<RFP_CONTRACT>Y</RFP_CONTRACT>
<PROPSALESCONT>gene kaliman</PROPSALESCONT>
<PROPSALESTITLE>Director of Programming</PROPSALESTITLE>
<PROPSALESPHONECOUNTRY>1</PROPSALESPHONECOUNTRY>
<PROPSALESPHONECITYCODE>619</PROPSALESPHONECITYCODE>
<PROPSALESPHONE>400-88412</PROPSALESPHONE>
<PROPSALESFAXCOUNTRYCODE />
<PROPSALESFAXCITYCODE>6193</PROPSALESFAXCITYCODE>
<PROPSALESFAX>400-88194</PROPSALESFAX>
<PROPSALESEMAIL>cirquedusoul@.cyberianrealty.net</PROPSALESE
MAIL>
<RFP_NAME>gene kaliman</RFP_NAME>
<RFP_TITLE>Director of Programming</RFP_TITLE>
<RFP_PHONECOUNTRYCODE>1</RFP_PHONECOUNTRYCODE>
<RFP_PHONECITYCODE>619</RFP_PHONECITYCODE>
<RFP_PHONE>400-88412</RFP_PHONE>
<RFP_EMAIL>cirquedusoul@.cyberianrealty.net</RFP_EMAIL>
<RFP_DATESUBMIT>2004-07-13</RFP_DATESUBMIT>
- <ns0:ROOM_TYPES>
<ROOMTYPEDEFINE>Standard</ROOMTYPEDEFINE>
<ROOMTYPENUMBER>0</ROOMTYPENUMBER>
</ns0:ROOM_TYPES>
- <ns0:ROOM_TYPES>
<ROOMTYPEDEFINE>Upgrade</ROOMTYPEDEFINE>
<ROOMTYPENUMBER>0</ROOMTYPENUMBER>
</ns0:ROOM_TYPES>
- <ns0:SEASON_RATES>
<SEASON_START>2002-01-01</SEASON_START>
<SEASON_END>2002-12-31</SEASON_END>
- <ns0:ROOM_RATES>
<ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
<RACK_SGL />
<RACK_DBL />
<CORP_SGL />
<CORP_DBL />
<LRA_SGL>123</LRA_SGL>
<LRA_DBL>123</LRA_DBL>
<NLRA_SGL />
<NLRA_DBL />
<CONS_SGL />
<CONS_DBL />
<GOVT_SGL />
<GOVT_DBL />
</ns0:ROOM_RATES>
- <ns0:ROOM_RATES>
<ROOM_TYPE_NUM>2</ROOM_TYPE_NUM>
<RACK_SGL />
<RACK_DBL />
<CORP_SGL />
<CORP_DBL />
<LRA_SGL>234</LRA_SGL>
<LRA_DBL>234</LRA_DBL>
<NLRA_SGL />
<NLRA_DBL />
<CONS_SGL />
<CONS_DBL />
<GOVT_SGL />
<GOVT_DBL />
</ns0:ROOM_RATES>
</ns0:SEASON_RATES>
</ns0:CS>
- <ns0:SA>
<REST_ONSITE>Y</REST_ONSITE>
<LOUNGE_BAR>N</LOUNGE_BAR>
<REST_MEALSBK>Y</REST_MEALSBK>
<REST_MEALSLUN>N</REST_MEALSLUN>
<REST_MEALSDIN>Y</REST_MEALSDIN>
<RM_SRV_AVL>N</RM_SRV_AVL>
<RM_SRV_HRS>01:00-08:01</RM_SRV_HRS>
<RECEPTION>N</RECEPTION>
<RECEPT_DAYS>Monday</RECEPT_DAYS>
<GIFT_NEWS>Y</GIFT_NEWS>
<CONVENENCE_FOOD>N</CONVENENCE_FOOD>
<GROCERY_SVC>Y</GROCERY_SVC>
<CASH_ONSITE>N</CASH_ONSITE>
<FITON_CENT>Y</FITON_CENT>
<FITON_TYPE />
<FITOFF_CENT>Y</FITOFF_CENT>
<FITOFF_TYPE>E</FITOFF_TYPE>
<REC_COURT>N</REC_COURT>
<CONC_SERV>N</CONC_SERV>
<BUS_CENTER>Y</BUS_CENTER>
<BUS_SERV>Y</BUS_SERV>
<IN_POOL>N</IN_POOL>
<OUT_POOL>Y</OUT_POOL>
<WHIRLPOOL>Y</WHIRLPOOL>
<SAUNA>Y</SAUNA>
<VIDEO_RENT>N</VIDEO_RENT>
<PARKING>N</PARKING>
<VALET_PARKING>N</VALET_PARKING>
<DAILY_PARK_FEE>5</DAILY_PARK_FEE>
<PARKFEE_MANAGED_HOTEL>N</PARKFEE_MANAGED_HOTEL>
<STORAGE>N</STORAGE>
<STORAGE_FEE />
<SAFEBOX>Y</SAFEBOX>
<LAUN_DRY>Y</LAUN_DRY>
<LAUNDRY_SITE>Y</LAUNDRY_SITE>
<LAUNDRY_HOURS>00:00 - 23:58</LAUNDRY_HOURS>
<IRON_BOARD>N</IRON_BOARD>
<HAIR_DRY>N</HAIR_DRY>
<ALM_CLOCK />
<TV_CABLE>N</TV_CABLE>
<TV_MOVIE>N</TV_MOVIE>
<STEREO>N</STEREO>
<VCR>N</VCR>
<DVD>N</DVD>
<MINI_FRIG>N</MINI_FRIG>
<MICRO>N</MICRO>
<COFF_TEA>N</COFF_TEA>
<MINIBAR>N</MINIBAR>
<PRESS>N</PRESS>
<WORKDESK>N</WORKDESK>
<NEWS_PAP>N</NEWS_PAP>
<BATHROBE>N</BATHROBE>
<SOFA_BED>N</SOFA_BED>
<IN_SAFE>N</IN_SAFE>
<SAFE_CHG>0</SAFE_CHG>
<AIR_CONDITIONED>N</AIR_CONDITIONED>
<IN_SELF_TEMP>N</IN_SELF_TEMP>
<PRIVATE_BATHSA>Y</PRIVATE_BATHSA>
<STOVE>N</STOVE>
<OVEN>N</OVEN>
<TOASTER>N</TOASTER>
<REFRIGERATOR>N</REFRIGERATOR>
<DISHWASHER>N</DISHWASHER>
<DINING_WARE>N</DINING_WARE>
<DISHES>N</DISHES>
<HOUSEKEEPING>W</HOUSEKEEPING>
<HOUSE_FULL_OR_LIM>L</HOUSE_FULL_OR_LIM>
<FRNT_DESK>00:00 - 23:57</FRNT_DESK>
</ns0:SA>
- <ns0:SS>
<ROOMSMKDET>Y</ROOMSMKDET>
<HALLSMKDET>NA</HALLSMKDET>
<PUBSMKDET>N</PUBSMKDET>
<HRDWIREDET>N</HRDWIREDET>
<FIRE_EXTIN>Y</FIRE_EXTIN>
<EMRGCYLITE>N</EMRGCYLITE>
<VENT_STAIR>N</VENT_STAIR>
<EVACPLAN>Y</EVACPLAN>
<FIRELAWS>N</FIRELAWS>
<VIS_ALARM>N</VIS_ALARM>
<HALLVIS_ALARM>NA</HALLVIS_ALARM>
<PUBVIS_ALARM>NA</PUBVIS_ALARM>
<AUD_ALARM>Y</AUD_ALARM>
<HALLAUD_ALARM>NA</HALLAUD_ALARM>
<PUBAUD_ALARM>NA</PUBAUD_ALARM>
<ROOMSPRINK>N</ROOMSPRINK>
<HALLSPRINK>N</HALLSPRINK>
<PUBSPRINK>N</PUBSPRINK>
<EMRGCYINFO>N</EMRGCYINFO>
<FIRE_LINK>N</FIRE_LINK>
<MULT_EXITS>Y</MULT_EXITS>
<AUTO_RECAL>N</AUTO_RECAL>
<DISABACCSS>N</DISABACCSS>
<NUMDISABL>3</NUMDISABL>
<HAND_PARK>Y</HAND_PARK>
<SECURITY>N</SECURITY>
<SECUR_HRS>00:00 - 23:59</SECUR_HRS>
<DEADBOLTS>N</DEADBOLTS>
<PUBADDRESS>Y</PUBADDRESS>
<INT_ACCESS>Y</INT_ACCESS>
<OPEN_WINDOWS>Y</OPEN_WINDOWS>
<SAFE_CODES>N</SAFE_CODES>
<DOORS_CLOSE>Y</DOORS_CLOSE>
<PARKGATES>NA</PARKGATES>
<PARKPATROL>N</PARKPATROL>
<PARKLIGHTS>NA</PARKLIGHTS>
<PUBCAMERA>N</PUBCAMERA>
<PARK_FR_ENT_CAMERA>NA</PARK_FR_ENT_CAMERA>
<CAMERA_REC_24HR>NA</CAMERA_REC_24HR>
<CAMERA_MONT_24HR>NA</CAMERA_MONT_24HR>
<VIEWPORTS>N</VIEWPORTS>
<BACK_UP>N</BACK_UP>
<ALARMS_CONT_MONT>N</ALARMS_CONT_MONT>
<EXT_DOOR_KEY_ACCESS>N</EXT_DOOR_KEY_ACCESS>
<STAFF_AID>Y</STAFF_AID>
<STAFF_CPR_TRAIN>N</STAFF_CPR_TRAIN>
<STAFF_CPR_CERT>N</STAFF_CPR_CERT>
<STAFF_AED>N</STAFF_AED>
<MEDICAL_EQUIP>N</MEDICAL_EQUIP>
<AED_ONSITE>N</AED_ONSITE>
</ns0:SS>
- <ns0:GT>
<TRAIN_NAME>23</TRAIN_NAME>
<DIST_TRAIN>0.10000000000000001</DIST_TRAIN>
<METRO_NAME />
<METRO_DIST>0</METRO_DIST>
<CITYTRANS>N</CITYTRANS>
<CARRENT>N</CARRENT>
<CARRENT_NAME>first</CARRENT_NAME>
- <ns0:AIRPORT>
<AIR_CODE>ANA</AIR_CODE>
<AIR_DIRECT>N</AIR_DIRECT>
<AIR_DIST>.00</AIR_DIST>
<AIR_SHUTTL>N</AIR_SHUTTL>
<AIR_TAXI>0</AIR_TAXI>
<AIR_PUB>N</AIR_PUB>
<DIRECTION />
</ns0:AIRPORT>
</ns0:GT>
- <ns0:CT>
<TWOLINE>Y</TWOLINE>
<VOICEMAIL>N</VOICEMAIL>
<DIRECT_DIAL>Y</DIRECT_DIAL>
<SPEAKPHONE>Y</SPEAKPHONE>
<CORDPHONE>Y</CORDPHONE>
<MOBILE_PH>Y</MOBILE_PH>
<RM_FAX>Y</RM_FAX>
<RM_PRINT>Y</RM_PRINT>
<ELEC_DESK_OUT>Y</ELEC_DESK_OUT>
<TEL_JACK_ADP>Y</TEL_JACK_ADP>
<ELEC_ADP>Y</ELEC_ADP>
<POWER_CONV>Y</POWER_CONV>
<NUM_PHONE>12</NUM_PHONE>
<PHONE_BILL_SEP>N</PHONE_BILL_SEP>
<IN_FAX>0</IN_FAX>
<IN_FAX_ADD>0</IN_FAX_ADD>
<IN_FAX_COMM />
<OUT_FAX>0</OUT_FAX>
<OUT_FAX_ADD>0</OUT_FAX_ADD>
<OUT_FAX_COMM />
<LOCALCALL_FEE />
<CCARD_FEE />
<OPER_FEE />
<TOLL_FEE />
<INTL_FEE />
<CARACC_FEE />
<AN_DATAPORT>Y</AN_DATAPORT>
<AN_DATAPORT_NUM>0</AN_DATAPORT_NUM>
<AN_DATAPORT_CHG>0</AN_DATAPORT_CHG>
<INTER_TV>Y</INTER_TV>
<INTER_TV_NUM>0</INTER_TV_NUM>
<INTER_TV_CHG>0</INTER_TV_CHG>
<HIGHSPEED_INROOM>Y</HIGHSPEED_INROOM>
<HIGHSPEED_INROOM_NUM>0</HIGHSPEED_INROOM_NUM>
<HIGHSPEED_INROOM_CHG>0</HIGHSPEED_INROOM_CHG>
<WIRELESS>Y</WIRELESS>
<WIRELESS_CHG>0</WIRELESS_CHG>
<INROOM_PC>N</INROOM_PC>
<INROOM_PC_CHG />
<AN_DATAPORT_BC>N</AN_DATAPORT_BC>
<AN_DATAPORT_BC_CHG />
<HIGHSPEED_BC>N</HIGHSPEED_BC>
<HIGHSPEED_BC_CHG />
<WIRELESS_BC>N</WIRELESS_BC>
<WIRELESS_BC_CHG />
<PC_BC>N</PC_BC>
<PC_BC_CHG />
<AN_DATAPORT_PA>Y</AN_DATAPORT_PA>
<AN_DATAPORT_PA_CHG />
<HIGHSPEED_PA>N</HIGHSPEED_PA>
<HIGHSPEED_PA_CHG />
<WIRELESS_PA>N</WIRELESS_PA>
<WIRELESS_PA_CHG />
<PC_PA>N</PC_PA>
<PC_PA_CHG />
<INDV_FOLIOS>Y</INDV_FOLIOS>
<FOLIOS_COMPANY>Y</FOLIOS_COMPANY>
</ns0:CT>
- <ns0:ES>
- <ns0:SEASON_RATES_ES>
<SEASON_START_ES>2002-01-01</SEASON_START_ES>
<SEASON_END_ES>2002-12-31</SEASON_END_ES>
- <ns0:LOS_TIER>
<LOS_TIER_NUM>2</LOS_TIER_NUM>
- <ns0:ROOM_RATES_ES>
<ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
<CORP_ES_SGL />
<CORP_ES_DBL />
<LRA_ES_SGL />
<LRA_ES_DBL />
<NLRA_ES_SGL />
<NLRA_ES_DBL />
<CONS_ES_SGL />
<CONS_ES_DBL />
</ns0:ROOM_RATES_ES>
- <ns0:ROOM_RATES_ES>
<ROOM_TYPE_NUM>2</ROOM_TYPE_NUM>
<CORP_ES_SGL />
<CORP_ES_DBL />
<LRA_ES_SGL />
<LRA_ES_DBL />
<NLRA_ES_SGL />
<NLRA_ES_DBL />
<CONS_ES_SGL />
<CONS_ES_DBL />
</ns0:ROOM_RATES_ES>
</ns0:LOS_TIER>
</ns0:SEASON_RATES_ES>
- <ns0:ADDTL_CHARGE_ES>
<ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
</ns0:ADDTL_CHARGE_ES>
</ns0:ES>
- <ns0:Errors>
- <ns0:Error1>
<FieldName>SEASON 1 SINGLE ROOM</FieldName>
<Description>Rack is empty or not an number; Corp is
empty or not an number; LRA is empty or not an number;
Consortia is empty or not an number;</Description>
</ns0:Error1>
- <ns0:Error2>
<FieldName>SEASON 1 DOUBLE ROOM</FieldName>
<Description>Rack is empty or not an number; Corp is
empty or not an number; LRA is empty or not an number;
Consortia is empty or not an number;</Description>
</ns0:Error2>
</ns0:Errors>
</ns0:X_NBTA05>The reason is that you set the default element namespace, but PROPCODE is
not in a namespace:
<PB xmlns="http://www.rfpexpress.com">
<PROPCODE xmlns="">34991</PROPCODE>
Try the following query (note I updated the syntax so it will work with
Beta3 and replaced your FLWR with a normal path expression since they are
equivalent):
SELECT bidData.query('
declare namespace r = "http://www.rfpexpress.com";
/r:X_NBTA05/r:PB/PROPCODE
')
FROM tblBidXMLData
HTH
Michael
"Bryan Murtha" <anonymous@.discussions.microsoft.com> wrote in message
news:160801c4e6cd$17c1d680$a401280a@.phx.gbl...
> Please, Please, Please help. I tried a million different
> ways of doing this and I can't get results, I always get
> no results or NULL. I just want a node, just one node,
> anything.
> use telnform
> SELECT bidData.query('
> declare default element namespace=
> "http://www.rfpexpress.com"
> for $propCode in /X_NBTA05/PB/PROPCODE
> return $propCode
> ')
> FROM tblBidXMLData
> USE [TelnForm]
> GO
> /****** Object: Table [dbo].[tblBidXMLData] Script
> Date: 12/20/2004 11:48:10 ******/
> SET ANSI_NULLS ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[tblBidXMLData](
> [BidID] [int] NOT NULL,
> [bidData] [xml] NULL,
> [CorpID] [int] NOT NULL,
> [CorpRFPNumber] [varchar](25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [PropID] [int] NOT NULL,
> [AutoSubmit] [bit] NOT NULL,
> [ValidationErrors] [int] NULL,
> [NamId] [int] NULL
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> GO
> SET ANSI_NULLS OFF
> - <ns0:X_NBTA05 xmlns:ns0="http://www.rfpexpress.com"
> xmlns:ns1="http://www.rfpexpress.com/Global">
> - <ns0:PB>
> <PROPCODE>34991</PROPCODE>
> <PROPNAME>RFP Express Test Property - Product
> Development</PROPNAME>
> <PROPADD1>8989 Rio San Diego Dr11</PROPADD1>
> <PROPADD2 />
> <PROPCITY>Ixataville</PROPCITY>
> <PROPSTATEPROV>CA</PROPSTATEPROV>
> <PROPCOUNTRY>United States</PROPCOUNTRY>
> <PROPPOSTCODE>99998</PROPPOSTCODE>
> <MAINPHONECOUNTRY />
> <MAINPHONECITY>619</MAINPHONECITY>
> <MAINPHONE>4008840</MAINPHONE>
> <MAINFAXCOUNTRY />
> <MAINFAXCITY>619</MAINFAXCITY>
> <MAINFAX>4008819</MAINFAX>
> <UNITOFMEAS>MI</UNITOFMEAS>
> <PROPURL>1</PROPURL>
> <FULL_OR_LIM>L</FULL_OR_LIM>
> <ES_STAY>Y</ES_STAY>
> <MKT_TIER>L</MKT_TIER>
> <AAADIAM_RATE>1</AAADIAM_RATE>
> <MOBILSTAR_RATE>NA</MOBILSTAR_RATE>
> <OHG_CLASS>Tourist</OHG_CLASS>
> <YRBUILT>1952</YRBUILT>
> <LASTREN>1234</LASTREN>
> <NUMROOMS>555</NUMROOMS>
> <NUMNOSMK>1</NUMNOSMK>
> <NUMFLRS>45</NUMFLRS>
> <MGMTCOMPANY>Gene Kaliman</MGMTCOMPANY>
> <OWNERCOMPANY>Gene Kaliman</OWNERCOMPANY>
> <MIN_OWNED_CERT>Y</MIN_OWNED_CERT>
> <WMN_OWNED_CERT>N</WMN_OWNED_CERT>
> <ADACOMPLY>N</ADACOMPLY>
> <PROP_LOC>T</PROP_LOC>
> <PROPOVERVIEW>Luxury hotel located in the heart of
> downtown San Diego. Lovely view of the
> harbor.</PROPOVERVIEW>
> <AMEXCARD>Y</AMEXCARD>
> <DINERSCARD>Y</DINERSCARD>
> <DISCOVERCARD>Y</DISCOVERCARD>
> <JCBCARD>Y</JCBCARD>
> <MASTERCARD>Y</MASTERCARD>
> <VISACARD>Y</VISACARD>
> <AMADEUS_CHAINCODE>67</AMADEUS_CHAINCODE>
> <AMADEUS_PROPCODE>555a</AMADEUS_PROPCODE>
> <APOLLO_CHAINCODE>12</APOLLO_CHAINCODE>
> <APOLLO_PROPCODE />
> <SABRE_CHAINCODE>14</SABRE_CHAINCODE>
> <SABRE_PROPCODE>15a</SABRE_PROPCODE>
> <WORLDSPAN_CHAINCODE>56</WORLDSPAN_CHAINCODE>
> <WORLDSPAN_PROPCODE />
> <MASTERCHAINCODE>AS</MASTERCHAINCODE>
> </ns0:PB>
> - <ns0:CS>
> <TEMPLATE_NAME>Consortia</TEMPLATE_NAME>
> <OFFICENAME />
> <OFFICEADD1 />
> <RN_TARGET>N</RN_TARGET>
> <RATE_CURR>USD</RATE_CURR>
> <COMMISIONABLE>N</COMMISIONABLE>
> <COMM_PCT_CLIENT />
> <COMM_PCT_CONS />
> <LOAD_GDS>Y</LOAD_GDS>
> <WALK_HOTEL>N</WALK_HOTEL>
> <WALK_TAX>N</WALK_TAX>
> <WALK_TRANS>N</WALK_TRANS>
> <WALK_CALL>N</WALK_CALL>
> <CANC_POL>24hr</CANC_POL>
> <EARLYCK_FEE>0</EARLYCK_FEE>
> <EARLYCK_UOM>F</EARLYCK_UOM>
> <EARLYCK_INCLUDE>N</EARLYCK_INCLUDE>
> <LODGTX_FEE>13</LODGTX_FEE>
> <LODGTX_UOM>P</LODGTX_UOM>
> <LODGTX_INCLUDE>N</LODGTX_INCLUDE>
> <STATETX_FEE>23</STATETX_FEE>
> <STATETX_UOM>P</STATETX_UOM>
> <STATETX_INCLUDE>Y</STATETX_INCLUDE>
> <CITYTX_FEE>34</CITYTX_FEE>
> <CITYTX_UOM>P</CITYTX_UOM>
> <CITYTX_INCLUDE>N</CITYTX_INCLUDE>
> <VATGST_FEE>45</VATGST_FEE>
> <VATGST_UOM>P</VATGST_UOM>
> <VATGST_INCLUDE>Y</VATGST_INCLUDE>
> <SERVICE_FEE>56</SERVICE_FEE>
> <SERVICE_UOM>P</SERVICE_UOM>
> <SERVICE_INCLUDE>N</SERVICE_INCLUDE>
> <OCC_FEE>76</OCC_FEE>
> <OCC_UOM>P</OCC_UOM>
> <OCC_INCLUDE>Y</OCC_INCLUDE>
> <PARK_FEE>5</PARK_FEE>
> <PARK_INCLUDE>N</PARK_INCLUDE>
> <BREAK_FEE />
> <BREAK_INCLUDE>N</BREAK_INCLUDE>
> <FITNESS_FEE_ON>234</FITNESS_FEE_ON>
> <FITNESS_INCLUDE_ON>N</FITNESS_INCLUDE_ON>
> <FITNESS_FEE_OFF>0</FITNESS_FEE_OFF>
> <FITNESS_INCLUDE_OFF>N</FITNESS_INCLUDE_OFF>
> <HSIA_FEE>0</HSIA_FEE>
> <HSIA_INCLUDE>N</HSIA_INCLUDE>
> <WIRELESS_FEE>0</WIRELESS_FEE>
> <WIRELESS_INCLUDE>N</WIRELESS_INCLUDE>
> <COMP_INFAX>N</COMP_INFAX>
> <COMP_OUTFAX>N</COMP_OUTFAX>
> <AIRTRANS_FEE>0</AIRTRANS_FEE>
> <AIRTRANS_INCLUDE>N</AIRTRANS_INCLUDE>
> <OFFTRANS_FEE>0</OFFTRANS_FEE>
> <OFFTRANS_INCLUDE>Y</OFFTRANS_INCLUDE>
> <RATE_SUBS>N</RATE_SUBS>
> <RATE_PERSO>N</RATE_PERSO>
> <RATE_CONSU>N</RATE_CONSU>
> <RATE_RETIR>N</RATE_RETIR>
> <RFP_CONTRACT>Y</RFP_CONTRACT>
> <PROPSALESCONT>gene kaliman</PROPSALESCONT>
> <PROPSALESTITLE>Director of Programming</PROPSALESTITLE>
> <PROPSALESPHONECOUNTRY>1</PROPSALESPHONECOUNTRY>
> <PROPSALESPHONECITYCODE>619</PROPSALESPHONECITYCODE>
> <PROPSALESPHONE>400-88412</PROPSALESPHONE>
> <PROPSALESFAXCOUNTRYCODE />
> <PROPSALESFAXCITYCODE>6193</PROPSALESFAXCITYCODE>
> <PROPSALESFAX>400-88194</PROPSALESFAX>
> <PROPSALESEMAIL>cirquedusoul@.cyberianrealty.net</PROPSALESE
> MAIL>
> <RFP_NAME>gene kaliman</RFP_NAME>
> <RFP_TITLE>Director of Programming</RFP_TITLE>
> <RFP_PHONECOUNTRYCODE>1</RFP_PHONECOUNTRYCODE>
> <RFP_PHONECITYCODE>619</RFP_PHONECITYCODE>
> <RFP_PHONE>400-88412</RFP_PHONE>
> <RFP_EMAIL>cirquedusoul@.cyberianrealty.net</RFP_EMAIL>
> <RFP_DATESUBMIT>2004-07-13</RFP_DATESUBMIT>
> - <ns0:ROOM_TYPES>
> <ROOMTYPEDEFINE>Standard</ROOMTYPEDEFINE>
> <ROOMTYPENUMBER>0</ROOMTYPENUMBER>
> </ns0:ROOM_TYPES>
> - <ns0:ROOM_TYPES>
> <ROOMTYPEDEFINE>Upgrade</ROOMTYPEDEFINE>
> <ROOMTYPENUMBER>0</ROOMTYPENUMBER>
> </ns0:ROOM_TYPES>
> - <ns0:SEASON_RATES>
> <SEASON_START>2002-01-01</SEASON_START>
> <SEASON_END>2002-12-31</SEASON_END>
> - <ns0:ROOM_RATES>
> <ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
> <RACK_SGL />
> <RACK_DBL />
> <CORP_SGL />
> <CORP_DBL />
> <LRA_SGL>123</LRA_SGL>
> <LRA_DBL>123</LRA_DBL>
> <NLRA_SGL />
> <NLRA_DBL />
> <CONS_SGL />
> <CONS_DBL />
> <GOVT_SGL />
> <GOVT_DBL />
> </ns0:ROOM_RATES>
> - <ns0:ROOM_RATES>
> <ROOM_TYPE_NUM>2</ROOM_TYPE_NUM>
> <RACK_SGL />
> <RACK_DBL />
> <CORP_SGL />
> <CORP_DBL />
> <LRA_SGL>234</LRA_SGL>
> <LRA_DBL>234</LRA_DBL>
> <NLRA_SGL />
> <NLRA_DBL />
> <CONS_SGL />
> <CONS_DBL />
> <GOVT_SGL />
> <GOVT_DBL />
> </ns0:ROOM_RATES>
> </ns0:SEASON_RATES>
> </ns0:CS>
> - <ns0:SA>
> <REST_ONSITE>Y</REST_ONSITE>
> <LOUNGE_BAR>N</LOUNGE_BAR>
> <REST_MEALSBK>Y</REST_MEALSBK>
> <REST_MEALSLUN>N</REST_MEALSLUN>
> <REST_MEALSDIN>Y</REST_MEALSDIN>
> <RM_SRV_AVL>N</RM_SRV_AVL>
> <RM_SRV_HRS>01:00-08:01</RM_SRV_HRS>
> <RECEPTION>N</RECEPTION>
> <RECEPT_DAYS>Monday</RECEPT_DAYS>
> <GIFT_NEWS>Y</GIFT_NEWS>
> <CONVENENCE_FOOD>N</CONVENENCE_FOOD>
> <GROCERY_SVC>Y</GROCERY_SVC>
> <CASH_ONSITE>N</CASH_ONSITE>
> <FITON_CENT>Y</FITON_CENT>
> <FITON_TYPE />
> <FITOFF_CENT>Y</FITOFF_CENT>
> <FITOFF_TYPE>E</FITOFF_TYPE>
> <REC_COURT>N</REC_COURT>
> <CONC_SERV>N</CONC_SERV>
> <BUS_CENTER>Y</BUS_CENTER>
> <BUS_SERV>Y</BUS_SERV>
> <IN_POOL>N</IN_POOL>
> <OUT_POOL>Y</OUT_POOL>
> <WHIRLPOOL>Y</WHIRLPOOL>
> <SAUNA>Y</SAUNA>
> <VIDEO_RENT>N</VIDEO_RENT>
> <PARKING>N</PARKING>
> <VALET_PARKING>N</VALET_PARKING>
> <DAILY_PARK_FEE>5</DAILY_PARK_FEE>
> <PARKFEE_MANAGED_HOTEL>N</PARKFEE_MANAGED_HOTEL>
> <STORAGE>N</STORAGE>
> <STORAGE_FEE />
> <SAFEBOX>Y</SAFEBOX>
> <LAUN_DRY>Y</LAUN_DRY>
> <LAUNDRY_SITE>Y</LAUNDRY_SITE>
> <LAUNDRY_HOURS>00:00 - 23:58</LAUNDRY_HOURS>
> <IRON_BOARD>N</IRON_BOARD>
> <HAIR_DRY>N</HAIR_DRY>
> <ALM_CLOCK />
> <TV_CABLE>N</TV_CABLE>
> <TV_MOVIE>N</TV_MOVIE>
> <STEREO>N</STEREO>
> <VCR>N</VCR>
> <DVD>N</DVD>
> <MINI_FRIG>N</MINI_FRIG>
> <MICRO>N</MICRO>
> <COFF_TEA>N</COFF_TEA>
> <MINIBAR>N</MINIBAR>
> <PRESS>N</PRESS>
> <WORKDESK>N</WORKDESK>
> <NEWS_PAP>N</NEWS_PAP>
> <BATHROBE>N</BATHROBE>
> <SOFA_BED>N</SOFA_BED>
> <IN_SAFE>N</IN_SAFE>
> <SAFE_CHG>0</SAFE_CHG>
> <AIR_CONDITIONED>N</AIR_CONDITIONED>
> <IN_SELF_TEMP>N</IN_SELF_TEMP>
> <PRIVATE_BATHSA>Y</PRIVATE_BATHSA>
> <STOVE>N</STOVE>
> <OVEN>N</OVEN>
> <TOASTER>N</TOASTER>
> <REFRIGERATOR>N</REFRIGERATOR>
> <DISHWASHER>N</DISHWASHER>
> <DINING_WARE>N</DINING_WARE>
> <DISHES>N</DISHES>
> <HOUSEKEEPING>W</HOUSEKEEPING>
> <HOUSE_FULL_OR_LIM>L</HOUSE_FULL_OR_LIM>
> <FRNT_DESK>00:00 - 23:57</FRNT_DESK>
> </ns0:SA>
> - <ns0:SS>
> <ROOMSMKDET>Y</ROOMSMKDET>
> <HALLSMKDET>NA</HALLSMKDET>
> <PUBSMKDET>N</PUBSMKDET>
> <HRDWIREDET>N</HRDWIREDET>
> <FIRE_EXTIN>Y</FIRE_EXTIN>
> <EMRGCYLITE>N</EMRGCYLITE>
> <VENT_STAIR>N</VENT_STAIR>
> <EVACPLAN>Y</EVACPLAN>
> <FIRELAWS>N</FIRELAWS>
> <VIS_ALARM>N</VIS_ALARM>
> <HALLVIS_ALARM>NA</HALLVIS_ALARM>
> <PUBVIS_ALARM>NA</PUBVIS_ALARM>
> <AUD_ALARM>Y</AUD_ALARM>
> <HALLAUD_ALARM>NA</HALLAUD_ALARM>
> <PUBAUD_ALARM>NA</PUBAUD_ALARM>
> <ROOMSPRINK>N</ROOMSPRINK>
> <HALLSPRINK>N</HALLSPRINK>
> <PUBSPRINK>N</PUBSPRINK>
> <EMRGCYINFO>N</EMRGCYINFO>
> <FIRE_LINK>N</FIRE_LINK>
> <MULT_EXITS>Y</MULT_EXITS>
> <AUTO_RECAL>N</AUTO_RECAL>
> <DISABACCSS>N</DISABACCSS>
> <NUMDISABL>3</NUMDISABL>
> <HAND_PARK>Y</HAND_PARK>
> <SECURITY>N</SECURITY>
> <SECUR_HRS>00:00 - 23:59</SECUR_HRS>
> <DEADBOLTS>N</DEADBOLTS>
> <PUBADDRESS>Y</PUBADDRESS>
> <INT_ACCESS>Y</INT_ACCESS>
> <OPEN_WINDOWS>Y</OPEN_WINDOWS>
> <SAFE_CODES>N</SAFE_CODES>
> <DOORS_CLOSE>Y</DOORS_CLOSE>
> <PARKGATES>NA</PARKGATES>
> <PARKPATROL>N</PARKPATROL>
> <PARKLIGHTS>NA</PARKLIGHTS>
> <PUBCAMERA>N</PUBCAMERA>
> <PARK_FR_ENT_CAMERA>NA</PARK_FR_ENT_CAMERA>
> <CAMERA_REC_24HR>NA</CAMERA_REC_24HR>
> <CAMERA_MONT_24HR>NA</CAMERA_MONT_24HR>
> <VIEWPORTS>N</VIEWPORTS>
> <BACK_UP>N</BACK_UP>
> <ALARMS_CONT_MONT>N</ALARMS_CONT_MONT>
> <EXT_DOOR_KEY_ACCESS>N</EXT_DOOR_KEY_ACCESS>
> <STAFF_AID>Y</STAFF_AID>
> <STAFF_CPR_TRAIN>N</STAFF_CPR_TRAIN>
> <STAFF_CPR_CERT>N</STAFF_CPR_CERT>
> <STAFF_AED>N</STAFF_AED>
> <MEDICAL_EQUIP>N</MEDICAL_EQUIP>
> <AED_ONSITE>N</AED_ONSITE>
> </ns0:SS>
> - <ns0:GT>
> <TRAIN_NAME>23</TRAIN_NAME>
> <DIST_TRAIN>0.10000000000000001</DIST_TRAIN>
> <METRO_NAME />
> <METRO_DIST>0</METRO_DIST>
> <CITYTRANS>N</CITYTRANS>
> <CARRENT>N</CARRENT>
> <CARRENT_NAME>first</CARRENT_NAME>
> - <ns0:AIRPORT>
> <AIR_CODE>ANA</AIR_CODE>
> <AIR_DIRECT>N</AIR_DIRECT>
> <AIR_DIST>.00</AIR_DIST>
> <AIR_SHUTTL>N</AIR_SHUTTL>
> <AIR_TAXI>0</AIR_TAXI>
> <AIR_PUB>N</AIR_PUB>
> <DIRECTION />
> </ns0:AIRPORT>
> </ns0:GT>
> - <ns0:CT>
> <TWOLINE>Y</TWOLINE>
> <VOICEMAIL>N</VOICEMAIL>
> <DIRECT_DIAL>Y</DIRECT_DIAL>
> <SPEAKPHONE>Y</SPEAKPHONE>
> <CORDPHONE>Y</CORDPHONE>
> <MOBILE_PH>Y</MOBILE_PH>
> <RM_FAX>Y</RM_FAX>
> <RM_PRINT>Y</RM_PRINT>
> <ELEC_DESK_OUT>Y</ELEC_DESK_OUT>
> <TEL_JACK_ADP>Y</TEL_JACK_ADP>
> <ELEC_ADP>Y</ELEC_ADP>
> <POWER_CONV>Y</POWER_CONV>
> <NUM_PHONE>12</NUM_PHONE>
> <PHONE_BILL_SEP>N</PHONE_BILL_SEP>
> <IN_FAX>0</IN_FAX>
> <IN_FAX_ADD>0</IN_FAX_ADD>
> <IN_FAX_COMM />
> <OUT_FAX>0</OUT_FAX>
> <OUT_FAX_ADD>0</OUT_FAX_ADD>
> <OUT_FAX_COMM />
> <LOCALCALL_FEE />
> <CCARD_FEE />
> <OPER_FEE />
> <TOLL_FEE />
> <INTL_FEE />
> <CARACC_FEE />
> <AN_DATAPORT>Y</AN_DATAPORT>
> <AN_DATAPORT_NUM>0</AN_DATAPORT_NUM>
> <AN_DATAPORT_CHG>0</AN_DATAPORT_CHG>
> <INTER_TV>Y</INTER_TV>
> <INTER_TV_NUM>0</INTER_TV_NUM>
> <INTER_TV_CHG>0</INTER_TV_CHG>
> <HIGHSPEED_INROOM>Y</HIGHSPEED_INROOM>
> <HIGHSPEED_INROOM_NUM>0</HIGHSPEED_INROOM_NUM>
> <HIGHSPEED_INROOM_CHG>0</HIGHSPEED_INROOM_CHG>
> <WIRELESS>Y</WIRELESS>
> <WIRELESS_CHG>0</WIRELESS_CHG>
> <INROOM_PC>N</INROOM_PC>
> <INROOM_PC_CHG />
> <AN_DATAPORT_BC>N</AN_DATAPORT_BC>
> <AN_DATAPORT_BC_CHG />
> <HIGHSPEED_BC>N</HIGHSPEED_BC>
> <HIGHSPEED_BC_CHG />
> <WIRELESS_BC>N</WIRELESS_BC>
> <WIRELESS_BC_CHG />
> <PC_BC>N</PC_BC>
> <PC_BC_CHG />
> <AN_DATAPORT_PA>Y</AN_DATAPORT_PA>
> <AN_DATAPORT_PA_CHG />
> <HIGHSPEED_PA>N</HIGHSPEED_PA>
> <HIGHSPEED_PA_CHG />
> <WIRELESS_PA>N</WIRELESS_PA>
> <WIRELESS_PA_CHG />
> <PC_PA>N</PC_PA>
> <PC_PA_CHG />
> <INDV_FOLIOS>Y</INDV_FOLIOS>
> <FOLIOS_COMPANY>Y</FOLIOS_COMPANY>
> </ns0:CT>
> - <ns0:ES>
> - <ns0:SEASON_RATES_ES>
> <SEASON_START_ES>2002-01-01</SEASON_START_ES>
> <SEASON_END_ES>2002-12-31</SEASON_END_ES>
> - <ns0:LOS_TIER>
> <LOS_TIER_NUM>2</LOS_TIER_NUM>
> - <ns0:ROOM_RATES_ES>
> <ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
> <CORP_ES_SGL />
> <CORP_ES_DBL />
> <LRA_ES_SGL />
> <LRA_ES_DBL />
> <NLRA_ES_SGL />
> <NLRA_ES_DBL />
> <CONS_ES_SGL />
> <CONS_ES_DBL />
> </ns0:ROOM_RATES_ES>
> - <ns0:ROOM_RATES_ES>
> <ROOM_TYPE_NUM>2</ROOM_TYPE_NUM>
> <CORP_ES_SGL />
> <CORP_ES_DBL />
> <LRA_ES_SGL />
> <LRA_ES_DBL />
> <NLRA_ES_SGL />
> <NLRA_ES_DBL />
> <CONS_ES_SGL />
> <CONS_ES_DBL />
> </ns0:ROOM_RATES_ES>
> </ns0:LOS_TIER>
> </ns0:SEASON_RATES_ES>
> - <ns0:ADDTL_CHARGE_ES>
> <ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
> </ns0:ADDTL_CHARGE_ES>
> </ns0:ES>
> - <ns0:Errors>
> - <ns0:Error1>
> <FieldName>SEASON 1 SINGLE ROOM</FieldName>
> <Description>Rack is empty or not an number; Corp is
> empty or not an number; LRA is empty or not an number;
> Consortia is empty or not an number;</Description>
> </ns0:Error1>
> - <ns0:Error2>
> <FieldName>SEASON 1 DOUBLE ROOM</FieldName>
> <Description>Rack is empty or not an number; Corp is
> empty or not an number; LRA is empty or not an number;
> Consortia is empty or not an number;</Description>
> </ns0:Error2>
> </ns0:Errors>
> </ns0:X_NBTA05>

Always get null, can't return results

Please, Please, Please help. I tried a million different
ways of doing this and I can't get results, I always get
no results or NULL. I just want a node, just one node,
anything.
use telnform
SELECT bidData.query('
declare default element namespace=
"http://www.rfpexpress.com"
for $propCode in /X_NBTA05/PB/PROPCODE
return $propCode
')
FROM tblBidXMLData
USE [TelnForm]
GO
/****** Object: Table [dbo].[tblBidXMLData] Script
Date: 12/20/2004 11:48:10 ******/
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblBidXMLData](
[BidID] [int] NOT NULL,
[bidData] [xml] NULL,
[CorpID] [int] NOT NULL,
[CorpRFPNumber] [varchar](25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PropID] [int] NOT NULL,
[AutoSubmit] [bit] NOT NULL,
[ValidationErrors] [int] NULL,
[NamId] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS OFF
- <ns0:X_NBTA05 xmlns:ns0="http://www.rfpexpress.com"
xmlns:ns1="http://www.rfpexpress.com/Global">
- <ns0:PB>
<PROPCODE>34991</PROPCODE>
<PROPNAME>RFP Express Test Property - Product
Development</PROPNAME>
<PROPADD1>8989 Rio San Diego Dr11</PROPADD1>
<PROPADD2 />
<PROPCITY>Ixataville</PROPCITY>
<PROPSTATEPROV>CA</PROPSTATEPROV>
<PROPCOUNTRY>United States</PROPCOUNTRY>
<PROPPOSTCODE>99998</PROPPOSTCODE>
<MAINPHONECOUNTRY />
<MAINPHONECITY>619</MAINPHONECITY>
<MAINPHONE>4008840</MAINPHONE>
<MAINFAXCOUNTRY />
<MAINFAXCITY>619</MAINFAXCITY>
<MAINFAX>4008819</MAINFAX>
<UNITOFMEAS>MI</UNITOFMEAS>
<PROPURL>1</PROPURL>
<FULL_OR_LIM>L</FULL_OR_LIM>
<ES_STAY>Y</ES_STAY>
<MKT_TIER>L</MKT_TIER>
<AAADIAM_RATE>1</AAADIAM_RATE>
<MOBILSTAR_RATE>NA</MOBILSTAR_RATE>
<OHG_CLASS>Tourist</OHG_CLASS>
<YRBUILT>1952</YRBUILT>
<LASTREN>1234</LASTREN>
<NUMROOMS>555</NUMROOMS>
<NUMNOSMK>1</NUMNOSMK>
<NUMFLRS>45</NUMFLRS>
<MGMTCOMPANY>Gene Kaliman</MGMTCOMPANY>
<OWNERCOMPANY>Gene Kaliman</OWNERCOMPANY>
<MIN_OWNED_CERT>Y</MIN_OWNED_CERT>
<WMN_OWNED_CERT>N</WMN_OWNED_CERT>
<ADACOMPLY>N</ADACOMPLY>
<PROP_LOC>T</PROP_LOC>
<PROPOVERVIEW>Luxury hotel located in the heart of
downtown San Diego. Lovely view of the
harbor.</PROPOVERVIEW>
<AMEXCARD>Y</AMEXCARD>
<DINERSCARD>Y</DINERSCARD>
<DISCOVERCARD>Y</DISCOVERCARD>
<JCBCARD>Y</JCBCARD>
<MASTERCARD>Y</MASTERCARD>
<VISACARD>Y</VISACARD>
<AMADEUS_CHAINCODE>67</AMADEUS_CHAINCODE>
<AMADEUS_PROPCODE>555a</AMADEUS_PROPCODE>
<APOLLO_CHAINCODE>12</APOLLO_CHAINCODE>
<APOLLO_PROPCODE />
<SABRE_CHAINCODE>14</SABRE_CHAINCODE>
<SABRE_PROPCODE>15a</SABRE_PROPCODE>
<WORLDSPAN_CHAINCODE>56</WORLDSPAN_CHAINCODE>
<WORLDSPAN_PROPCODE />
<MASTERCHAINCODE>AS</MASTERCHAINCODE>
</ns0:PB>
- <ns0:CS>
<TEMPLATE_NAME>Consortia</TEMPLATE_NAME>
<OFFICENAME />
<OFFICEADD1 />
<RN_TARGET>N</RN_TARGET>
<RATE_CURR>USD</RATE_CURR>
<COMMISIONABLE>N</COMMISIONABLE>
<COMM_PCT_CLIENT />
<COMM_PCT_CONS />
<LOAD_GDS>Y</LOAD_GDS>
<WALK_HOTEL>N</WALK_HOTEL>
<WALK_TAX>N</WALK_TAX>
<WALK_TRANS>N</WALK_TRANS>
<WALK_CALL>N</WALK_CALL>
<CANC_POL>24hr</CANC_POL>
<EARLYCK_FEE>0</EARLYCK_FEE>
<EARLYCK_UOM>F</EARLYCK_UOM>
<EARLYCK_INCLUDE>N</EARLYCK_INCLUDE>
<LODGTX_FEE>13</LODGTX_FEE>
<LODGTX_UOM>P</LODGTX_UOM>
<LODGTX_INCLUDE>N</LODGTX_INCLUDE>
<STATETX_FEE>23</STATETX_FEE>
<STATETX_UOM>P</STATETX_UOM>
<STATETX_INCLUDE>Y</STATETX_INCLUDE>
<CITYTX_FEE>34</CITYTX_FEE>
<CITYTX_UOM>P</CITYTX_UOM>
<CITYTX_INCLUDE>N</CITYTX_INCLUDE>
<VATGST_FEE>45</VATGST_FEE>
<VATGST_UOM>P</VATGST_UOM>
<VATGST_INCLUDE>Y</VATGST_INCLUDE>
<SERVICE_FEE>56</SERVICE_FEE>
<SERVICE_UOM>P</SERVICE_UOM>
<SERVICE_INCLUDE>N</SERVICE_INCLUDE>
<OCC_FEE>76</OCC_FEE>
<OCC_UOM>P</OCC_UOM>
<OCC_INCLUDE>Y</OCC_INCLUDE>
<PARK_FEE>5</PARK_FEE>
<PARK_INCLUDE>N</PARK_INCLUDE>
<BREAK_FEE />
<BREAK_INCLUDE>N</BREAK_INCLUDE>
<FITNESS_FEE_ON>234</FITNESS_FEE_ON>
<FITNESS_INCLUDE_ON>N</FITNESS_INCLUDE_ON>
<FITNESS_FEE_OFF>0</FITNESS_FEE_OFF>
<FITNESS_INCLUDE_OFF>N</FITNESS_INCLUDE_OFF>
<HSIA_FEE>0</HSIA_FEE>
<HSIA_INCLUDE>N</HSIA_INCLUDE>
<WIRELESS_FEE>0</WIRELESS_FEE>
<WIRELESS_INCLUDE>N</WIRELESS_INCLUDE>
<COMP_INFAX>N</COMP_INFAX>
<COMP_OUTFAX>N</COMP_OUTFAX>
<AIRTRANS_FEE>0</AIRTRANS_FEE>
<AIRTRANS_INCLUDE>N</AIRTRANS_INCLUDE>
<OFFTRANS_FEE>0</OFFTRANS_FEE>
<OFFTRANS_INCLUDE>Y</OFFTRANS_INCLUDE>
<RATE_SUBS>N</RATE_SUBS>
<RATE_PERSO>N</RATE_PERSO>
<RATE_CONSU>N</RATE_CONSU>
<RATE_RETIR>N</RATE_RETIR>
<RFP_CONTRACT>Y</RFP_CONTRACT>
<PROPSALESCONT>gene kaliman</PROPSALESCONT>
<PROPSALESTITLE>Director of Programming</PROPSALESTITLE>
<PROPSALESPHONECOUNTRY>1</PROPSALESPHONECOUNTRY>
<PROPSALESPHONECITYCODE>619</PROPSALESPHONECITYCODE>
<PROPSALESPHONE>400-88412</PROPSALESPHONE>
<PROPSALESFAXCOUNTRYCODE />
<PROPSALESFAXCITYCODE>6193</PROPSALESFAXCITYCODE>
<PROPSALESFAX>400-88194</PROPSALESFAX>
<PROPSALESEMAIL>cirquedusoul@.cyberianrealty.net< /PROPSALESE
MAIL>
<RFP_NAME>gene kaliman</RFP_NAME>
<RFP_TITLE>Director of Programming</RFP_TITLE>
<RFP_PHONECOUNTRYCODE>1</RFP_PHONECOUNTRYCODE>
<RFP_PHONECITYCODE>619</RFP_PHONECITYCODE>
<RFP_PHONE>400-88412</RFP_PHONE>
<RFP_EMAIL>cirquedusoul@.cyberianrealty.net</RFP_EMAIL>
<RFP_DATESUBMIT>2004-07-13</RFP_DATESUBMIT>
- <ns0:ROOM_TYPES>
<ROOMTYPEDEFINE>Standard</ROOMTYPEDEFINE>
<ROOMTYPENUMBER>0</ROOMTYPENUMBER>
</ns0:ROOM_TYPES>
- <ns0:ROOM_TYPES>
<ROOMTYPEDEFINE>Upgrade</ROOMTYPEDEFINE>
<ROOMTYPENUMBER>0</ROOMTYPENUMBER>
</ns0:ROOM_TYPES>
- <ns0:SEASON_RATES>
<SEASON_START>2002-01-01</SEASON_START>
<SEASON_END>2002-12-31</SEASON_END>
- <ns0:ROOM_RATES>
<ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
<RACK_SGL />
<RACK_DBL />
<CORP_SGL />
<CORP_DBL />
<LRA_SGL>123</LRA_SGL>
<LRA_DBL>123</LRA_DBL>
<NLRA_SGL />
<NLRA_DBL />
<CONS_SGL />
<CONS_DBL />
<GOVT_SGL />
<GOVT_DBL />
</ns0:ROOM_RATES>
- <ns0:ROOM_RATES>
<ROOM_TYPE_NUM>2</ROOM_TYPE_NUM>
<RACK_SGL />
<RACK_DBL />
<CORP_SGL />
<CORP_DBL />
<LRA_SGL>234</LRA_SGL>
<LRA_DBL>234</LRA_DBL>
<NLRA_SGL />
<NLRA_DBL />
<CONS_SGL />
<CONS_DBL />
<GOVT_SGL />
<GOVT_DBL />
</ns0:ROOM_RATES>
</ns0:SEASON_RATES>
</ns0:CS>
- <ns0:SA>
<REST_ONSITE>Y</REST_ONSITE>
<LOUNGE_BAR>N</LOUNGE_BAR>
<REST_MEALSBK>Y</REST_MEALSBK>
<REST_MEALSLUN>N</REST_MEALSLUN>
<REST_MEALSDIN>Y</REST_MEALSDIN>
<RM_SRV_AVL>N</RM_SRV_AVL>
<RM_SRV_HRS>01:00-08:01</RM_SRV_HRS>
<RECEPTION>N</RECEPTION>
<RECEPT_DAYS>Monday</RECEPT_DAYS>
<GIFT_NEWS>Y</GIFT_NEWS>
<CONVENENCE_FOOD>N</CONVENENCE_FOOD>
<GROCERY_SVC>Y</GROCERY_SVC>
<CASH_ONSITE>N</CASH_ONSITE>
<FITON_CENT>Y</FITON_CENT>
<FITON_TYPE />
<FITOFF_CENT>Y</FITOFF_CENT>
<FITOFF_TYPE>E</FITOFF_TYPE>
<REC_COURT>N</REC_COURT>
<CONC_SERV>N</CONC_SERV>
<BUS_CENTER>Y</BUS_CENTER>
<BUS_SERV>Y</BUS_SERV>
<IN_POOL>N</IN_POOL>
<OUT_POOL>Y</OUT_POOL>
<WHIRLPOOL>Y</WHIRLPOOL>
<SAUNA>Y</SAUNA>
<VIDEO_RENT>N</VIDEO_RENT>
<PARKING>N</PARKING>
<VALET_PARKING>N</VALET_PARKING>
<DAILY_PARK_FEE>5</DAILY_PARK_FEE>
<PARKFEE_MANAGED_HOTEL>N</PARKFEE_MANAGED_HOTEL>
<STORAGE>N</STORAGE>
<STORAGE_FEE />
<SAFEBOX>Y</SAFEBOX>
<LAUN_DRY>Y</LAUN_DRY>
<LAUNDRY_SITE>Y</LAUNDRY_SITE>
<LAUNDRY_HOURS>00:00 - 23:58</LAUNDRY_HOURS>
<IRON_BOARD>N</IRON_BOARD>
<HAIR_DRY>N</HAIR_DRY>
<ALM_CLOCK />
<TV_CABLE>N</TV_CABLE>
<TV_MOVIE>N</TV_MOVIE>
<STEREO>N</STEREO>
<VCR>N</VCR>
<DVD>N</DVD>
<MINI_FRIG>N</MINI_FRIG>
<MICRO>N</MICRO>
<COFF_TEA>N</COFF_TEA>
<MINIBAR>N</MINIBAR>
<PRESS>N</PRESS>
<WORKDESK>N</WORKDESK>
<NEWS_PAP>N</NEWS_PAP>
<BATHROBE>N</BATHROBE>
<SOFA_BED>N</SOFA_BED>
<IN_SAFE>N</IN_SAFE>
<SAFE_CHG>0</SAFE_CHG>
<AIR_CONDITIONED>N</AIR_CONDITIONED>
<IN_SELF_TEMP>N</IN_SELF_TEMP>
<PRIVATE_BATHSA>Y</PRIVATE_BATHSA>
<STOVE>N</STOVE>
<OVEN>N</OVEN>
<TOASTER>N</TOASTER>
<REFRIGERATOR>N</REFRIGERATOR>
<DISHWASHER>N</DISHWASHER>
<DINING_WARE>N</DINING_WARE>
<DISHES>N</DISHES>
<HOUSEKEEPING>W</HOUSEKEEPING>
<HOUSE_FULL_OR_LIM>L</HOUSE_FULL_OR_LIM>
<FRNT_DESK>00:00 - 23:57</FRNT_DESK>
</ns0:SA>
- <ns0:SS>
<ROOMSMKDET>Y</ROOMSMKDET>
<HALLSMKDET>NA</HALLSMKDET>
<PUBSMKDET>N</PUBSMKDET>
<HRDWIREDET>N</HRDWIREDET>
<FIRE_EXTIN>Y</FIRE_EXTIN>
<EMRGCYLITE>N</EMRGCYLITE>
<VENT_STAIR>N</VENT_STAIR>
<EVACPLAN>Y</EVACPLAN>
<FIRELAWS>N</FIRELAWS>
<VIS_ALARM>N</VIS_ALARM>
<HALLVIS_ALARM>NA</HALLVIS_ALARM>
<PUBVIS_ALARM>NA</PUBVIS_ALARM>
<AUD_ALARM>Y</AUD_ALARM>
<HALLAUD_ALARM>NA</HALLAUD_ALARM>
<PUBAUD_ALARM>NA</PUBAUD_ALARM>
<ROOMSPRINK>N</ROOMSPRINK>
<HALLSPRINK>N</HALLSPRINK>
<PUBSPRINK>N</PUBSPRINK>
<EMRGCYINFO>N</EMRGCYINFO>
<FIRE_LINK>N</FIRE_LINK>
<MULT_EXITS>Y</MULT_EXITS>
<AUTO_RECAL>N</AUTO_RECAL>
<DISABACCSS>N</DISABACCSS>
<NUMDISABL>3</NUMDISABL>
<HAND_PARK>Y</HAND_PARK>
<SECURITY>N</SECURITY>
<SECUR_HRS>00:00 - 23:59</SECUR_HRS>
<DEADBOLTS>N</DEADBOLTS>
<PUBADDRESS>Y</PUBADDRESS>
<INT_ACCESS>Y</INT_ACCESS>
<OPEN_WINDOWS>Y</OPEN_WINDOWS>
<SAFE_CODES>N</SAFE_CODES>
<DOORS_CLOSE>Y</DOORS_CLOSE>
<PARKGATES>NA</PARKGATES>
<PARKPATROL>N</PARKPATROL>
<PARKLIGHTS>NA</PARKLIGHTS>
<PUBCAMERA>N</PUBCAMERA>
<PARK_FR_ENT_CAMERA>NA</PARK_FR_ENT_CAMERA>
<CAMERA_REC_24HR>NA</CAMERA_REC_24HR>
<CAMERA_MONT_24HR>NA</CAMERA_MONT_24HR>
<VIEWPORTS>N</VIEWPORTS>
<BACK_UP>N</BACK_UP>
<ALARMS_CONT_MONT>N</ALARMS_CONT_MONT>
<EXT_DOOR_KEY_ACCESS>N</EXT_DOOR_KEY_ACCESS>
<STAFF_AID>Y</STAFF_AID>
<STAFF_CPR_TRAIN>N</STAFF_CPR_TRAIN>
<STAFF_CPR_CERT>N</STAFF_CPR_CERT>
<STAFF_AED>N</STAFF_AED>
<MEDICAL_EQUIP>N</MEDICAL_EQUIP>
<AED_ONSITE>N</AED_ONSITE>
</ns0:SS>
- <ns0:GT>
<TRAIN_NAME>23</TRAIN_NAME>
<DIST_TRAIN>0.10000000000000001</DIST_TRAIN>
<METRO_NAME />
<METRO_DIST>0</METRO_DIST>
<CITYTRANS>N</CITYTRANS>
<CARRENT>N</CARRENT>
<CARRENT_NAME>first</CARRENT_NAME>
- <ns0:AIRPORT>
<AIR_CODE>ANA</AIR_CODE>
<AIR_DIRECT>N</AIR_DIRECT>
<AIR_DIST>.00</AIR_DIST>
<AIR_SHUTTL>N</AIR_SHUTTL>
<AIR_TAXI>0</AIR_TAXI>
<AIR_PUB>N</AIR_PUB>
<DIRECTION />
</ns0:AIRPORT>
</ns0:GT>
- <ns0:CT>
<TWOLINE>Y</TWOLINE>
<VOICEMAIL>N</VOICEMAIL>
<DIRECT_DIAL>Y</DIRECT_DIAL>
<SPEAKPHONE>Y</SPEAKPHONE>
<CORDPHONE>Y</CORDPHONE>
<MOBILE_PH>Y</MOBILE_PH>
<RM_FAX>Y</RM_FAX>
<RM_PRINT>Y</RM_PRINT>
<ELEC_DESK_OUT>Y</ELEC_DESK_OUT>
<TEL_JACK_ADP>Y</TEL_JACK_ADP>
<ELEC_ADP>Y</ELEC_ADP>
<POWER_CONV>Y</POWER_CONV>
<NUM_PHONE>12</NUM_PHONE>
<PHONE_BILL_SEP>N</PHONE_BILL_SEP>
<IN_FAX>0</IN_FAX>
<IN_FAX_ADD>0</IN_FAX_ADD>
<IN_FAX_COMM />
<OUT_FAX>0</OUT_FAX>
<OUT_FAX_ADD>0</OUT_FAX_ADD>
<OUT_FAX_COMM />
<LOCALCALL_FEE />
<CCARD_FEE />
<OPER_FEE />
<TOLL_FEE />
<INTL_FEE />
<CARACC_FEE />
<AN_DATAPORT>Y</AN_DATAPORT>
<AN_DATAPORT_NUM>0</AN_DATAPORT_NUM>
<AN_DATAPORT_CHG>0</AN_DATAPORT_CHG>
<INTER_TV>Y</INTER_TV>
<INTER_TV_NUM>0</INTER_TV_NUM>
<INTER_TV_CHG>0</INTER_TV_CHG>
<HIGHSPEED_INROOM>Y</HIGHSPEED_INROOM>
<HIGHSPEED_INROOM_NUM>0</HIGHSPEED_INROOM_NUM>
<HIGHSPEED_INROOM_CHG>0</HIGHSPEED_INROOM_CHG>
<WIRELESS>Y</WIRELESS>
<WIRELESS_CHG>0</WIRELESS_CHG>
<INROOM_PC>N</INROOM_PC>
<INROOM_PC_CHG />
<AN_DATAPORT_BC>N</AN_DATAPORT_BC>
<AN_DATAPORT_BC_CHG />
<HIGHSPEED_BC>N</HIGHSPEED_BC>
<HIGHSPEED_BC_CHG />
<WIRELESS_BC>N</WIRELESS_BC>
<WIRELESS_BC_CHG />
<PC_BC>N</PC_BC>
<PC_BC_CHG />
<AN_DATAPORT_PA>Y</AN_DATAPORT_PA>
<AN_DATAPORT_PA_CHG />
<HIGHSPEED_PA>N</HIGHSPEED_PA>
<HIGHSPEED_PA_CHG />
<WIRELESS_PA>N</WIRELESS_PA>
<WIRELESS_PA_CHG />
<PC_PA>N</PC_PA>
<PC_PA_CHG />
<INDV_FOLIOS>Y</INDV_FOLIOS>
<FOLIOS_COMPANY>Y</FOLIOS_COMPANY>
</ns0:CT>
- <ns0:ES>
- <ns0:SEASON_RATES_ES>
<SEASON_START_ES>2002-01-01</SEASON_START_ES>
<SEASON_END_ES>2002-12-31</SEASON_END_ES>
- <ns0:LOS_TIER>
<LOS_TIER_NUM>2</LOS_TIER_NUM>
- <ns0:ROOM_RATES_ES>
<ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
<CORP_ES_SGL />
<CORP_ES_DBL />
<LRA_ES_SGL />
<LRA_ES_DBL />
<NLRA_ES_SGL />
<NLRA_ES_DBL />
<CONS_ES_SGL />
<CONS_ES_DBL />
</ns0:ROOM_RATES_ES>
- <ns0:ROOM_RATES_ES>
<ROOM_TYPE_NUM>2</ROOM_TYPE_NUM>
<CORP_ES_SGL />
<CORP_ES_DBL />
<LRA_ES_SGL />
<LRA_ES_DBL />
<NLRA_ES_SGL />
<NLRA_ES_DBL />
<CONS_ES_SGL />
<CONS_ES_DBL />
</ns0:ROOM_RATES_ES>
</ns0:LOS_TIER>
</ns0:SEASON_RATES_ES>
- <ns0:ADDTL_CHARGE_ES>
<ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
</ns0:ADDTL_CHARGE_ES>
</ns0:ES>
- <ns0:Errors>
- <ns0:Error1>
<FieldName>SEASON 1 SINGLE ROOM</FieldName>
<Description>Rack is empty or not an number; Corp is
empty or not an number; LRA is empty or not an number;
Consortia is empty or not an number;</Description>
</ns0:Error1>
- <ns0:Error2>
<FieldName>SEASON 1 DOUBLE ROOM</FieldName>
<Description>Rack is empty or not an number; Corp is
empty or not an number; LRA is empty or not an number;
Consortia is empty or not an number;</Description>
</ns0:Error2>
</ns0:Errors>
</ns0:X_NBTA05>
The reason is that you set the default element namespace, but PROPCODE is
not in a namespace:
<PB xmlns="http://www.rfpexpress.com">
<PROPCODE xmlns="">34991</PROPCODE>
Try the following query (note I updated the syntax so it will work with
Beta3 and replaced your FLWR with a normal path expression since they are
equivalent):
SELECT bidData.query('
declare namespace r = "http://www.rfpexpress.com";
/r:X_NBTA05/r:PB/PROPCODE
')
FROM tblBidXMLData
HTH
Michael
"Bryan Murtha" <anonymous@.discussions.microsoft.com> wrote in message
news:160801c4e6cd$17c1d680$a401280a@.phx.gbl...
> Please, Please, Please help. I tried a million different
> ways of doing this and I can't get results, I always get
> no results or NULL. I just want a node, just one node,
> anything.
> use telnform
> SELECT bidData.query('
> declare default element namespace=
> "http://www.rfpexpress.com"
> for $propCode in /X_NBTA05/PB/PROPCODE
> return $propCode
> ')
> FROM tblBidXMLData
> USE [TelnForm]
> GO
> /****** Object: Table [dbo].[tblBidXMLData] Script
> Date: 12/20/2004 11:48:10 ******/
> SET ANSI_NULLS ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[tblBidXMLData](
> [BidID] [int] NOT NULL,
> [bidData] [xml] NULL,
> [CorpID] [int] NOT NULL,
> [CorpRFPNumber] [varchar](25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [PropID] [int] NOT NULL,
> [AutoSubmit] [bit] NOT NULL,
> [ValidationErrors] [int] NULL,
> [NamId] [int] NULL
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> GO
> SET ANSI_NULLS OFF
> - <ns0:X_NBTA05 xmlns:ns0="http://www.rfpexpress.com"
> xmlns:ns1="http://www.rfpexpress.com/Global">
> - <ns0:PB>
> <PROPCODE>34991</PROPCODE>
> <PROPNAME>RFP Express Test Property - Product
> Development</PROPNAME>
> <PROPADD1>8989 Rio San Diego Dr11</PROPADD1>
> <PROPADD2 />
> <PROPCITY>Ixataville</PROPCITY>
> <PROPSTATEPROV>CA</PROPSTATEPROV>
> <PROPCOUNTRY>United States</PROPCOUNTRY>
> <PROPPOSTCODE>99998</PROPPOSTCODE>
> <MAINPHONECOUNTRY />
> <MAINPHONECITY>619</MAINPHONECITY>
> <MAINPHONE>4008840</MAINPHONE>
> <MAINFAXCOUNTRY />
> <MAINFAXCITY>619</MAINFAXCITY>
> <MAINFAX>4008819</MAINFAX>
> <UNITOFMEAS>MI</UNITOFMEAS>
> <PROPURL>1</PROPURL>
> <FULL_OR_LIM>L</FULL_OR_LIM>
> <ES_STAY>Y</ES_STAY>
> <MKT_TIER>L</MKT_TIER>
> <AAADIAM_RATE>1</AAADIAM_RATE>
> <MOBILSTAR_RATE>NA</MOBILSTAR_RATE>
> <OHG_CLASS>Tourist</OHG_CLASS>
> <YRBUILT>1952</YRBUILT>
> <LASTREN>1234</LASTREN>
> <NUMROOMS>555</NUMROOMS>
> <NUMNOSMK>1</NUMNOSMK>
> <NUMFLRS>45</NUMFLRS>
> <MGMTCOMPANY>Gene Kaliman</MGMTCOMPANY>
> <OWNERCOMPANY>Gene Kaliman</OWNERCOMPANY>
> <MIN_OWNED_CERT>Y</MIN_OWNED_CERT>
> <WMN_OWNED_CERT>N</WMN_OWNED_CERT>
> <ADACOMPLY>N</ADACOMPLY>
> <PROP_LOC>T</PROP_LOC>
> <PROPOVERVIEW>Luxury hotel located in the heart of
> downtown San Diego. Lovely view of the
> harbor.</PROPOVERVIEW>
> <AMEXCARD>Y</AMEXCARD>
> <DINERSCARD>Y</DINERSCARD>
> <DISCOVERCARD>Y</DISCOVERCARD>
> <JCBCARD>Y</JCBCARD>
> <MASTERCARD>Y</MASTERCARD>
> <VISACARD>Y</VISACARD>
> <AMADEUS_CHAINCODE>67</AMADEUS_CHAINCODE>
> <AMADEUS_PROPCODE>555a</AMADEUS_PROPCODE>
> <APOLLO_CHAINCODE>12</APOLLO_CHAINCODE>
> <APOLLO_PROPCODE />
> <SABRE_CHAINCODE>14</SABRE_CHAINCODE>
> <SABRE_PROPCODE>15a</SABRE_PROPCODE>
> <WORLDSPAN_CHAINCODE>56</WORLDSPAN_CHAINCODE>
> <WORLDSPAN_PROPCODE />
> <MASTERCHAINCODE>AS</MASTERCHAINCODE>
> </ns0:PB>
> - <ns0:CS>
> <TEMPLATE_NAME>Consortia</TEMPLATE_NAME>
> <OFFICENAME />
> <OFFICEADD1 />
> <RN_TARGET>N</RN_TARGET>
> <RATE_CURR>USD</RATE_CURR>
> <COMMISIONABLE>N</COMMISIONABLE>
> <COMM_PCT_CLIENT />
> <COMM_PCT_CONS />
> <LOAD_GDS>Y</LOAD_GDS>
> <WALK_HOTEL>N</WALK_HOTEL>
> <WALK_TAX>N</WALK_TAX>
> <WALK_TRANS>N</WALK_TRANS>
> <WALK_CALL>N</WALK_CALL>
> <CANC_POL>24hr</CANC_POL>
> <EARLYCK_FEE>0</EARLYCK_FEE>
> <EARLYCK_UOM>F</EARLYCK_UOM>
> <EARLYCK_INCLUDE>N</EARLYCK_INCLUDE>
> <LODGTX_FEE>13</LODGTX_FEE>
> <LODGTX_UOM>P</LODGTX_UOM>
> <LODGTX_INCLUDE>N</LODGTX_INCLUDE>
> <STATETX_FEE>23</STATETX_FEE>
> <STATETX_UOM>P</STATETX_UOM>
> <STATETX_INCLUDE>Y</STATETX_INCLUDE>
> <CITYTX_FEE>34</CITYTX_FEE>
> <CITYTX_UOM>P</CITYTX_UOM>
> <CITYTX_INCLUDE>N</CITYTX_INCLUDE>
> <VATGST_FEE>45</VATGST_FEE>
> <VATGST_UOM>P</VATGST_UOM>
> <VATGST_INCLUDE>Y</VATGST_INCLUDE>
> <SERVICE_FEE>56</SERVICE_FEE>
> <SERVICE_UOM>P</SERVICE_UOM>
> <SERVICE_INCLUDE>N</SERVICE_INCLUDE>
> <OCC_FEE>76</OCC_FEE>
> <OCC_UOM>P</OCC_UOM>
> <OCC_INCLUDE>Y</OCC_INCLUDE>
> <PARK_FEE>5</PARK_FEE>
> <PARK_INCLUDE>N</PARK_INCLUDE>
> <BREAK_FEE />
> <BREAK_INCLUDE>N</BREAK_INCLUDE>
> <FITNESS_FEE_ON>234</FITNESS_FEE_ON>
> <FITNESS_INCLUDE_ON>N</FITNESS_INCLUDE_ON>
> <FITNESS_FEE_OFF>0</FITNESS_FEE_OFF>
> <FITNESS_INCLUDE_OFF>N</FITNESS_INCLUDE_OFF>
> <HSIA_FEE>0</HSIA_FEE>
> <HSIA_INCLUDE>N</HSIA_INCLUDE>
> <WIRELESS_FEE>0</WIRELESS_FEE>
> <WIRELESS_INCLUDE>N</WIRELESS_INCLUDE>
> <COMP_INFAX>N</COMP_INFAX>
> <COMP_OUTFAX>N</COMP_OUTFAX>
> <AIRTRANS_FEE>0</AIRTRANS_FEE>
> <AIRTRANS_INCLUDE>N</AIRTRANS_INCLUDE>
> <OFFTRANS_FEE>0</OFFTRANS_FEE>
> <OFFTRANS_INCLUDE>Y</OFFTRANS_INCLUDE>
> <RATE_SUBS>N</RATE_SUBS>
> <RATE_PERSO>N</RATE_PERSO>
> <RATE_CONSU>N</RATE_CONSU>
> <RATE_RETIR>N</RATE_RETIR>
> <RFP_CONTRACT>Y</RFP_CONTRACT>
> <PROPSALESCONT>gene kaliman</PROPSALESCONT>
> <PROPSALESTITLE>Director of Programming</PROPSALESTITLE>
> <PROPSALESPHONECOUNTRY>1</PROPSALESPHONECOUNTRY>
> <PROPSALESPHONECITYCODE>619</PROPSALESPHONECITYCODE>
> <PROPSALESPHONE>400-88412</PROPSALESPHONE>
> <PROPSALESFAXCOUNTRYCODE />
> <PROPSALESFAXCITYCODE>6193</PROPSALESFAXCITYCODE>
> <PROPSALESFAX>400-88194</PROPSALESFAX>
> <PROPSALESEMAIL>cirquedusoul@.cyberianrealty.net< /PROPSALESE
> MAIL>
> <RFP_NAME>gene kaliman</RFP_NAME>
> <RFP_TITLE>Director of Programming</RFP_TITLE>
> <RFP_PHONECOUNTRYCODE>1</RFP_PHONECOUNTRYCODE>
> <RFP_PHONECITYCODE>619</RFP_PHONECITYCODE>
> <RFP_PHONE>400-88412</RFP_PHONE>
> <RFP_EMAIL>cirquedusoul@.cyberianrealty.net</RFP_EMAIL>
> <RFP_DATESUBMIT>2004-07-13</RFP_DATESUBMIT>
> - <ns0:ROOM_TYPES>
> <ROOMTYPEDEFINE>Standard</ROOMTYPEDEFINE>
> <ROOMTYPENUMBER>0</ROOMTYPENUMBER>
> </ns0:ROOM_TYPES>
> - <ns0:ROOM_TYPES>
> <ROOMTYPEDEFINE>Upgrade</ROOMTYPEDEFINE>
> <ROOMTYPENUMBER>0</ROOMTYPENUMBER>
> </ns0:ROOM_TYPES>
> - <ns0:SEASON_RATES>
> <SEASON_START>2002-01-01</SEASON_START>
> <SEASON_END>2002-12-31</SEASON_END>
> - <ns0:ROOM_RATES>
> <ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
> <RACK_SGL />
> <RACK_DBL />
> <CORP_SGL />
> <CORP_DBL />
> <LRA_SGL>123</LRA_SGL>
> <LRA_DBL>123</LRA_DBL>
> <NLRA_SGL />
> <NLRA_DBL />
> <CONS_SGL />
> <CONS_DBL />
> <GOVT_SGL />
> <GOVT_DBL />
> </ns0:ROOM_RATES>
> - <ns0:ROOM_RATES>
> <ROOM_TYPE_NUM>2</ROOM_TYPE_NUM>
> <RACK_SGL />
> <RACK_DBL />
> <CORP_SGL />
> <CORP_DBL />
> <LRA_SGL>234</LRA_SGL>
> <LRA_DBL>234</LRA_DBL>
> <NLRA_SGL />
> <NLRA_DBL />
> <CONS_SGL />
> <CONS_DBL />
> <GOVT_SGL />
> <GOVT_DBL />
> </ns0:ROOM_RATES>
> </ns0:SEASON_RATES>
> </ns0:CS>
> - <ns0:SA>
> <REST_ONSITE>Y</REST_ONSITE>
> <LOUNGE_BAR>N</LOUNGE_BAR>
> <REST_MEALSBK>Y</REST_MEALSBK>
> <REST_MEALSLUN>N</REST_MEALSLUN>
> <REST_MEALSDIN>Y</REST_MEALSDIN>
> <RM_SRV_AVL>N</RM_SRV_AVL>
> <RM_SRV_HRS>01:00-08:01</RM_SRV_HRS>
> <RECEPTION>N</RECEPTION>
> <RECEPT_DAYS>Monday</RECEPT_DAYS>
> <GIFT_NEWS>Y</GIFT_NEWS>
> <CONVENENCE_FOOD>N</CONVENENCE_FOOD>
> <GROCERY_SVC>Y</GROCERY_SVC>
> <CASH_ONSITE>N</CASH_ONSITE>
> <FITON_CENT>Y</FITON_CENT>
> <FITON_TYPE />
> <FITOFF_CENT>Y</FITOFF_CENT>
> <FITOFF_TYPE>E</FITOFF_TYPE>
> <REC_COURT>N</REC_COURT>
> <CONC_SERV>N</CONC_SERV>
> <BUS_CENTER>Y</BUS_CENTER>
> <BUS_SERV>Y</BUS_SERV>
> <IN_POOL>N</IN_POOL>
> <OUT_POOL>Y</OUT_POOL>
> <WHIRLPOOL>Y</WHIRLPOOL>
> <SAUNA>Y</SAUNA>
> <VIDEO_RENT>N</VIDEO_RENT>
> <PARKING>N</PARKING>
> <VALET_PARKING>N</VALET_PARKING>
> <DAILY_PARK_FEE>5</DAILY_PARK_FEE>
> <PARKFEE_MANAGED_HOTEL>N</PARKFEE_MANAGED_HOTEL>
> <STORAGE>N</STORAGE>
> <STORAGE_FEE />
> <SAFEBOX>Y</SAFEBOX>
> <LAUN_DRY>Y</LAUN_DRY>
> <LAUNDRY_SITE>Y</LAUNDRY_SITE>
> <LAUNDRY_HOURS>00:00 - 23:58</LAUNDRY_HOURS>
> <IRON_BOARD>N</IRON_BOARD>
> <HAIR_DRY>N</HAIR_DRY>
> <ALM_CLOCK />
> <TV_CABLE>N</TV_CABLE>
> <TV_MOVIE>N</TV_MOVIE>
> <STEREO>N</STEREO>
> <VCR>N</VCR>
> <DVD>N</DVD>
> <MINI_FRIG>N</MINI_FRIG>
> <MICRO>N</MICRO>
> <COFF_TEA>N</COFF_TEA>
> <MINIBAR>N</MINIBAR>
> <PRESS>N</PRESS>
> <WORKDESK>N</WORKDESK>
> <NEWS_PAP>N</NEWS_PAP>
> <BATHROBE>N</BATHROBE>
> <SOFA_BED>N</SOFA_BED>
> <IN_SAFE>N</IN_SAFE>
> <SAFE_CHG>0</SAFE_CHG>
> <AIR_CONDITIONED>N</AIR_CONDITIONED>
> <IN_SELF_TEMP>N</IN_SELF_TEMP>
> <PRIVATE_BATHSA>Y</PRIVATE_BATHSA>
> <STOVE>N</STOVE>
> <OVEN>N</OVEN>
> <TOASTER>N</TOASTER>
> <REFRIGERATOR>N</REFRIGERATOR>
> <DISHWASHER>N</DISHWASHER>
> <DINING_WARE>N</DINING_WARE>
> <DISHES>N</DISHES>
> <HOUSEKEEPING>W</HOUSEKEEPING>
> <HOUSE_FULL_OR_LIM>L</HOUSE_FULL_OR_LIM>
> <FRNT_DESK>00:00 - 23:57</FRNT_DESK>
> </ns0:SA>
> - <ns0:SS>
> <ROOMSMKDET>Y</ROOMSMKDET>
> <HALLSMKDET>NA</HALLSMKDET>
> <PUBSMKDET>N</PUBSMKDET>
> <HRDWIREDET>N</HRDWIREDET>
> <FIRE_EXTIN>Y</FIRE_EXTIN>
> <EMRGCYLITE>N</EMRGCYLITE>
> <VENT_STAIR>N</VENT_STAIR>
> <EVACPLAN>Y</EVACPLAN>
> <FIRELAWS>N</FIRELAWS>
> <VIS_ALARM>N</VIS_ALARM>
> <HALLVIS_ALARM>NA</HALLVIS_ALARM>
> <PUBVIS_ALARM>NA</PUBVIS_ALARM>
> <AUD_ALARM>Y</AUD_ALARM>
> <HALLAUD_ALARM>NA</HALLAUD_ALARM>
> <PUBAUD_ALARM>NA</PUBAUD_ALARM>
> <ROOMSPRINK>N</ROOMSPRINK>
> <HALLSPRINK>N</HALLSPRINK>
> <PUBSPRINK>N</PUBSPRINK>
> <EMRGCYINFO>N</EMRGCYINFO>
> <FIRE_LINK>N</FIRE_LINK>
> <MULT_EXITS>Y</MULT_EXITS>
> <AUTO_RECAL>N</AUTO_RECAL>
> <DISABACCSS>N</DISABACCSS>
> <NUMDISABL>3</NUMDISABL>
> <HAND_PARK>Y</HAND_PARK>
> <SECURITY>N</SECURITY>
> <SECUR_HRS>00:00 - 23:59</SECUR_HRS>
> <DEADBOLTS>N</DEADBOLTS>
> <PUBADDRESS>Y</PUBADDRESS>
> <INT_ACCESS>Y</INT_ACCESS>
> <OPEN_WINDOWS>Y</OPEN_WINDOWS>
> <SAFE_CODES>N</SAFE_CODES>
> <DOORS_CLOSE>Y</DOORS_CLOSE>
> <PARKGATES>NA</PARKGATES>
> <PARKPATROL>N</PARKPATROL>
> <PARKLIGHTS>NA</PARKLIGHTS>
> <PUBCAMERA>N</PUBCAMERA>
> <PARK_FR_ENT_CAMERA>NA</PARK_FR_ENT_CAMERA>
> <CAMERA_REC_24HR>NA</CAMERA_REC_24HR>
> <CAMERA_MONT_24HR>NA</CAMERA_MONT_24HR>
> <VIEWPORTS>N</VIEWPORTS>
> <BACK_UP>N</BACK_UP>
> <ALARMS_CONT_MONT>N</ALARMS_CONT_MONT>
> <EXT_DOOR_KEY_ACCESS>N</EXT_DOOR_KEY_ACCESS>
> <STAFF_AID>Y</STAFF_AID>
> <STAFF_CPR_TRAIN>N</STAFF_CPR_TRAIN>
> <STAFF_CPR_CERT>N</STAFF_CPR_CERT>
> <STAFF_AED>N</STAFF_AED>
> <MEDICAL_EQUIP>N</MEDICAL_EQUIP>
> <AED_ONSITE>N</AED_ONSITE>
> </ns0:SS>
> - <ns0:GT>
> <TRAIN_NAME>23</TRAIN_NAME>
> <DIST_TRAIN>0.10000000000000001</DIST_TRAIN>
> <METRO_NAME />
> <METRO_DIST>0</METRO_DIST>
> <CITYTRANS>N</CITYTRANS>
> <CARRENT>N</CARRENT>
> <CARRENT_NAME>first</CARRENT_NAME>
> - <ns0:AIRPORT>
> <AIR_CODE>ANA</AIR_CODE>
> <AIR_DIRECT>N</AIR_DIRECT>
> <AIR_DIST>.00</AIR_DIST>
> <AIR_SHUTTL>N</AIR_SHUTTL>
> <AIR_TAXI>0</AIR_TAXI>
> <AIR_PUB>N</AIR_PUB>
> <DIRECTION />
> </ns0:AIRPORT>
> </ns0:GT>
> - <ns0:CT>
> <TWOLINE>Y</TWOLINE>
> <VOICEMAIL>N</VOICEMAIL>
> <DIRECT_DIAL>Y</DIRECT_DIAL>
> <SPEAKPHONE>Y</SPEAKPHONE>
> <CORDPHONE>Y</CORDPHONE>
> <MOBILE_PH>Y</MOBILE_PH>
> <RM_FAX>Y</RM_FAX>
> <RM_PRINT>Y</RM_PRINT>
> <ELEC_DESK_OUT>Y</ELEC_DESK_OUT>
> <TEL_JACK_ADP>Y</TEL_JACK_ADP>
> <ELEC_ADP>Y</ELEC_ADP>
> <POWER_CONV>Y</POWER_CONV>
> <NUM_PHONE>12</NUM_PHONE>
> <PHONE_BILL_SEP>N</PHONE_BILL_SEP>
> <IN_FAX>0</IN_FAX>
> <IN_FAX_ADD>0</IN_FAX_ADD>
> <IN_FAX_COMM />
> <OUT_FAX>0</OUT_FAX>
> <OUT_FAX_ADD>0</OUT_FAX_ADD>
> <OUT_FAX_COMM />
> <LOCALCALL_FEE />
> <CCARD_FEE />
> <OPER_FEE />
> <TOLL_FEE />
> <INTL_FEE />
> <CARACC_FEE />
> <AN_DATAPORT>Y</AN_DATAPORT>
> <AN_DATAPORT_NUM>0</AN_DATAPORT_NUM>
> <AN_DATAPORT_CHG>0</AN_DATAPORT_CHG>
> <INTER_TV>Y</INTER_TV>
> <INTER_TV_NUM>0</INTER_TV_NUM>
> <INTER_TV_CHG>0</INTER_TV_CHG>
> <HIGHSPEED_INROOM>Y</HIGHSPEED_INROOM>
> <HIGHSPEED_INROOM_NUM>0</HIGHSPEED_INROOM_NUM>
> <HIGHSPEED_INROOM_CHG>0</HIGHSPEED_INROOM_CHG>
> <WIRELESS>Y</WIRELESS>
> <WIRELESS_CHG>0</WIRELESS_CHG>
> <INROOM_PC>N</INROOM_PC>
> <INROOM_PC_CHG />
> <AN_DATAPORT_BC>N</AN_DATAPORT_BC>
> <AN_DATAPORT_BC_CHG />
> <HIGHSPEED_BC>N</HIGHSPEED_BC>
> <HIGHSPEED_BC_CHG />
> <WIRELESS_BC>N</WIRELESS_BC>
> <WIRELESS_BC_CHG />
> <PC_BC>N</PC_BC>
> <PC_BC_CHG />
> <AN_DATAPORT_PA>Y</AN_DATAPORT_PA>
> <AN_DATAPORT_PA_CHG />
> <HIGHSPEED_PA>N</HIGHSPEED_PA>
> <HIGHSPEED_PA_CHG />
> <WIRELESS_PA>N</WIRELESS_PA>
> <WIRELESS_PA_CHG />
> <PC_PA>N</PC_PA>
> <PC_PA_CHG />
> <INDV_FOLIOS>Y</INDV_FOLIOS>
> <FOLIOS_COMPANY>Y</FOLIOS_COMPANY>
> </ns0:CT>
> - <ns0:ES>
> - <ns0:SEASON_RATES_ES>
> <SEASON_START_ES>2002-01-01</SEASON_START_ES>
> <SEASON_END_ES>2002-12-31</SEASON_END_ES>
> - <ns0:LOS_TIER>
> <LOS_TIER_NUM>2</LOS_TIER_NUM>
> - <ns0:ROOM_RATES_ES>
> <ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
> <CORP_ES_SGL />
> <CORP_ES_DBL />
> <LRA_ES_SGL />
> <LRA_ES_DBL />
> <NLRA_ES_SGL />
> <NLRA_ES_DBL />
> <CONS_ES_SGL />
> <CONS_ES_DBL />
> </ns0:ROOM_RATES_ES>
> - <ns0:ROOM_RATES_ES>
> <ROOM_TYPE_NUM>2</ROOM_TYPE_NUM>
> <CORP_ES_SGL />
> <CORP_ES_DBL />
> <LRA_ES_SGL />
> <LRA_ES_DBL />
> <NLRA_ES_SGL />
> <NLRA_ES_DBL />
> <CONS_ES_SGL />
> <CONS_ES_DBL />
> </ns0:ROOM_RATES_ES>
> </ns0:LOS_TIER>
> </ns0:SEASON_RATES_ES>
> - <ns0:ADDTL_CHARGE_ES>
> <ROOM_TYPE_NUM>1</ROOM_TYPE_NUM>
> </ns0:ADDTL_CHARGE_ES>
> </ns0:ES>
> - <ns0:Errors>
> - <ns0:Error1>
> <FieldName>SEASON 1 SINGLE ROOM</FieldName>
> <Description>Rack is empty or not an number; Corp is
> empty or not an number; LRA is empty or not an number;
> Consortia is empty or not an number;</Description>
> </ns0:Error1>
> - <ns0:Error2>
> <FieldName>SEASON 1 DOUBLE ROOM</FieldName>
> <Description>Rack is empty or not an number; Corp is
> empty or not an number; LRA is empty or not an number;
> Consortia is empty or not an number;</Description>
> </ns0:Error2>
> </ns0:Errors>
> </ns0:X_NBTA05>

Alternative to dynamic sql?

I have a procedure that take several paramters and depending of what
values is submitted or not, the procedures shall return different
number of rows. But to simplyfy this my example use just one
parameter, for example Idnr.

If this id is submitted then I will return only the posts with this
idnr, but if this is not submitted, I will return all posts in table.
As I can see I have two options
1. IF @.lcIdNr IS NOT NULL
SELECT *
FROM table
WHERE idnr = @.lcIdNr
ELSE
SELECT *
FROM table

2. Use dynamic SQL.

The first example can work with just one parameter but with a couple
of different input paramters this could be difficult, anyway this is
not a good solution. The second example works fine but as I understand
dynamic sql is not good from the optimizing point of view. So, I don't
want to use either of theese options, so I wonder If there i a way to
work around this with for example a case clause?

Regards
JennyMaybe:

SELECT *
FROM TableX
WHERE idnr = @.lcIdNr OR @.lcIdNr IS NULL

This article explains some of the things you should consider before using
Dynamic SQL:

http://www.algonet.se/~sommar/dynamic_sql.html

--
David Portas
----
Please reply only to the newsgroup
--|||SELECT *
FROM Foobar
WHERE idnr = COALESCE(@.lcIdNr, idnr);|||[posted and mailed, vnligen svara i nys]

Jenny (jenny@.megasol.se) writes:
> If this id is submitted then I will return only the posts with this
> idnr, but if this is not submitted, I will return all posts in table.
> As I can see I have two options
> 1. IF @.lcIdNr IS NOT NULL
> SELECT *
> FROM table
> WHERE idnr = @.lcIdNr
> ELSE
> SELECT *
> FROM table
> 2. Use dynamic SQL.
> The first example can work with just one parameter but with a couple
> of different input paramters this could be difficult, anyway this is
> not a good solution. The second example works fine but as I understand
> dynamic sql is not good from the optimizing point of view.

Actually in this case it's the opposite. For these kind of queries,
dynamic SQL usually gives you the best combination performance and
maintainability.

For a longer discussion on the topic, see this article on my web site:
http://www.algonet.se/~sommar/dyn-search.html. (This is not the same
that David referred you too.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp