Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




XPATH Query


Consider this XML extract i have in VB6 using MSXML3

<medicalrecord>
  <contactlist>
    <contact>
      <contactid>1</contactid>
      <date>01012005</date>
      <code>
        <value>S33</value>
        <type>READ2</type>
      </code>
    </contact>
    <contact>
      <contactid>2</contactid>
      <date>02022005</date>
      <code>
        <value>H33</value>
        <type>READ2</type>
      </code>
    </contact>
  </contactlist>
</medicalrecord>

This is a very simple example. If it were all this simple we could just navigate the list manually to get the data.
However, it should be more efficient to use xpath to get a list of nodes that we want.

What we are looking for is the contact id and the value for each contact.
So the result node list might be something like...

<contact>
  <date>01012005</date>
  <value>S33</value>
</contact>
<contact>
  <date>02022005</date>
  <value>H33</value>
</contact>

The ultimate aim is to then translate this into a flat text file (but dont worry about that, though it might be useful to know why).

I am using MSXML3 in VB and the selectNodes method of a nodelist to get the resultant data set but that shouldnt matter.
Any suggestions or efficent method of processing?




Edited by - huwdm on 12/21/2005 2:32:18 AM




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
How Do I Change An XPath Query Expression
In the code below, how would I get the XPath query expression [.='Dave'] to be entered via a TextBox - I don't want it to be hard coded to "Dave", I want a user to be able to search with their own criteria.


VB Code:
Private Sub Command4_Click() Dim xmlDoc As New MSXML2.DOMDocument40Dim objNodeList As IXMLDOMNodeListxmlDoc.async = FalsexmlDoc.Load ("c:Employees.xml") xmlDoc.setProperty "SelectionLanguage", "XPath"Set objNodeList = xmlDoc.documentElement.selectNodes_("//EMPLOYEE/FIRSTNAME[b][.='Dave'][/b]")MsgBox objNodeList.length End Sub

XPath In VB6
I have an xml :

<?xml version="1.0"?>
<root>
<TEST NAME="Test1" DUT="1"/>
<TEST NAME="Test2" DUT="2"/>
</root>

I thought the XPath code should be something like:

Code:
"//TEST/@NAME="Test2""
or

Code:
"//TEST[@NAME="Test2"]"
in

Code:
Set GRPNodeList = xmlDOM.selectNodes("//TEST/@NAME="Test2"" )
For Each GRPNode In GRPNodeList
' Do something
iInnerCnt = iInnerCnt
Next
but both failed. Anyone?

Xpath Help
Below is a snippet of an XML doc that I am trying to search and return the node for a specific criterea. For example I would like to return the probability when Outs =0, Balls =0, OUT=0, Inning =1, TOP_OR_BOTTOM=1 RUNNERS_ON_BASE =000 and RUNS_AHEAD = -10. I have been looking at XPATH samples but I haven't figured it out. As always thank you for your help.


Code:
<?xml version="1.0" ?>
- <ROWDATA>
- <ROW>
<OUTS>0</OUTS>
<RUNNERS_ON_BASE>000</RUNNERS_ON_BASE>
<INNING>1</INNING>
<TOP_OR_BOTTOM>1</TOP_OR_BOTTOM>
<RUNS_AHEAD_BEHIND>-10</RUNS_AHEAD_BEHIND>
<BALLS>0</BALLS>
<STRIKES>0</STRIKES>
<PROBABILITY>0.654</PROBABILITY>
</ROW>
- <ROW>
<OUTS>0</OUTS>
<RUNNERS_ON_BASE>100</RUNNERS_ON_BASE>
<INNING>1</INNING>
<TOP_OR_BOTTOM>1</TOP_OR_BOTTOM>
<RUNS_AHEAD_BEHIND>-10</RUNS_AHEAD_BEHIND>
<BALLS>0</BALLS>
<STRIKES>0</STRIKES>
<PROBABILITY>0.76524</PROBABILITY>
</ROW>
- <ROW>
<OUTS>0</OUTS>
<RUNNERS_ON_BASE>020</RUNNERS_ON_BASE>
<INNING>1</INNING>
<TOP_OR_BOTTOM>1</TOP_OR_BOTTOM>
<RUNS_AHEAD_BEHIND>-10</RUNS_AHEAD_BEHIND>
<BALLS>0</BALLS>
<STRIKES>0</STRIKES>
<PROBABILITY>0.6624</PROBABILITY>
</ROW>
- <ROW>
<OUTS>0</OUTS>
<RUNNERS_ON_BASE>003</RUNNERS_ON_BASE>
<INNING>1</INNING>
<TOP_OR_BOTTOM>1</TOP_OR_BOTTOM>
<RUNS_AHEAD_BEHIND>-10</RUNS_AHEAD_BEHIND>
<BALLS>0</BALLS>
<STRIKES>0</STRIKES>
<PROBABILITY>0.21224</PROBABILITY>
</ROW>
- <ROW>
<OUTS>0</OUTS>
<RUNNERS_ON_BASE>120</RUNNERS_ON_BASE>
<INNING>1</INNING>
<TOP_OR_BOTTOM>1</TOP_OR_BOTTOM>
<RUNS_AHEAD_BEHIND>-10</RUNS_AHEAD_BEHIND>
<BALLS>0</BALLS>
<STRIKES>0</STRIKES>
<PROBABILITY>0.01238</PROBABILITY>
</ROW>
</ROWDATA>

Help With Xpath/VB6
Not sure if I should be posting it here, or in the XML forum, but here goes;
I need to parse and find certain records where the record name contains a certain string.
For example, I have this XML:


<B04_CALIPER>
<AAIA_ID>1368109</AAIA_ID>
<MODEL>A4 QUATTRO</MODEL>
<Nameplate/>
<Submodel/>
<Appl>Caliper to Bracket</Appl>
<FTorq>18</FTorq>
<RTorq>22</RTorq>
<Note/>
</B04_CALIPER>

<B06_LINING>
<AAIA_ID>1368109</AAIA_ID>
<Model_Name>A4 QUATTRO</Model_Name>
<Nameplate/>
<Submodel>STD Caliper</Submodel>
<Loc>F</Loc>
<Loc_Text>Front</Loc_Text>
<MiniPad>.080</MiniPad>
<MinShoe/>
<NotP/>
<NotS/>
</B06_LINING>

<B06_LINING>
<AAIA_ID>1368109</AAIA_ID>
<Model_Name>A4 QUATTRO</Model_Name>
<Nameplate/>
<Submodel/>
<Loc>R</Loc>
<Loc_Text>Rear</Loc_Text>
<MiniPad>.276</MiniPad>
<MinShoe/>
<NotP>Includes backing plate. </NotP>
<NotS/>
</B06_LINING>
-<B06_LINING>
<AAIA_ID>1368109</AAIA_ID>
<Model_Name>A4 QUATTRO</Model_Name>
<Nameplate/>
<Submodel>W/ Dual Piston</Submodel>
<Loc>F</Loc>
<Loc_Text>Front</Loc_Text>
<MiniPad>.120</MiniPad>
<MinShoe/>
<NotP/>
<NotS/>
</B06_LINING>

<B07_ROTOR>
<AAIA_ID>1368109</AAIA_ID>
<Model_Name>A4 QUATTRO</Model_Name>
<Nameplate/>
<Submodel/>
<Pos>R</Pos>
<Pos_Text>Rear</Pos_Text>
<MaxPar>NS</MaxPar>
<NotP/>
<Runout>NS</Runout>
<NotR/>
<NomT>.390</NomT>
<NotT/>
<MinMach>NS</MinMach>
<NotM/>
<Discard>.310</Discard>
<NotD/>
<Finish>NS</Finish>
<NotF/>
</B07_ROTOR>

How do I go about selecting all nodes that have the keyword LINING in the record name using XPATH?
The only way I could figure out was to use the exact name which is B06_LINING, but I need to be able to search just by LINING. Any suggestions?

XPath Xml In VB
This is the sort of xml i have got:

<Item ID="1">
<SubItem ID="1" Text="title1"/>
<SubItem ID="2" Text="title2"/>
</Item>

how can i use XPath with SelectNodes() to get all items that contain both SubItem with ID=1 and SubItem with ID=2?
Note that i need the Item, not its SubItems.

VB And XPath?
Hy.
I have a project that use as resource file an XML file with 5000 lines or more
To search a specified text resource I use XPath command selectSingleNode but it is working hard(it is load very hard).It is working hard and if I try to find the value by the hard way using "for" statement.
Could anyone work with vb and xml xpath?How can I made this to work fast like it works with a resource file.res?
don't give me the idee to use .res file, because I don't want to use .res file.
Please I will appreciate any help
Thanks
Avy

Xml And Xpath
Hello,

I'm trying to understan how DOM works.

I have xml etc.:


Code:
<root>
<file>
<Filename>file1.txt</Filename>
<Atributes>
<Atribute>ReadOnly</Atribute>
<Atribute>Hidden</Atribute>
</Atributes>
</file>
<file>
<Filename>file2.txt</Filename>
<Atributes>
<Atribute>Hidden</Atribute>
</Atributes>
</file>
</root>


To select file "file2.txt" I use this code:


VB Code:
Dim oXMLDoc As MSXML2.DOMDocument40Dim oXMLNode As MSXML2.IXMLDOMNodeDim oXMLNodeList As MSXML2.IXMLDOMNodeListSet oXMLDoc = New MSXML2.DOMDocument40 oXMLDoc.async = FalseoXMLDoc.resolveExternals = True oXMLDoc.Load ("C:files.xml")oXMLDoc.setProperty "SelectionLanguage", "XPath" Set oXMLNodeList = oXMLDoc.selectNodes("/root/file[Filename='file2.txt']") If oXMLNodeList.length <> 1 Thenexit sub 'there is 0 or more files with same filenameelse'now I want to read all atributes to string For Each oXMLNode In oXMLNodeList(0).selectNodes("//Atribute")Atributes = Atributes & oXMLNode.Text  & ", "Next end if


It retunrs "ReadOnly, Hidden, Hidden, " but not only result for that file ("Hidden")

I do know want to use loops, becourse it must be as fast as can be.

VB And XPath
Hello Everyone!!
I Try to use function contains() of Xpath in vb and get error:
"Unknown method" and "\queryItem
ame[contains(.<--,'Exec')]
Please tell me what's wrong in this code?




The vb sentence is:

Public str_path As String
Public exitfunc As Boolean
Public SearchData As String
Public Language As String


Private Sub Command1_Click()

Dim oXml As MSXML2.DOMDocument
Dim oNodeList As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
Dim TempNode As MSXML2.IXMLDOMNode
Dim oNodeNS As MSXML2.IXMLDOMNode
Dim oNo As MSXML2.IXMLDOMNode

Dim oNodeQS As MSXML2.IXMLDOMNode
Dim root As MSXML2.IXMLDOMNode
'Dim SearchData As String
Dim par_node As String

On Error GoTo ErrHandler
Set oXml = New MSXML2.DOMDocument
Call oXml.Load("c:model.xml")
SearchData = txt_search.Text
Language = Lenguage.Text

Set oNodeList = oXml.selectNodes("//queryItem/name[contains(.,'Exec')]")
MsgBox oNodeList.length
For Each oNode In oNodeList
exitfunc = False
Call F_Get_Path(oNode, True)
Next
Exit Sub
ErrHandler:


MsgBox Err.Description

End Sub



And the xml source that I use is:



<?xml version="1.0" encoding="UTF-8" ?>
<project xmlns="http://www.developer.cognos.com/schemas/bmt/32/1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.developer.cognos.com/schemas/bmt/32/1 BMTModelSpecification.xsd">
<name>Cibc</name>
<locales>
<locale>en</locale>
</locales>
<defaultLocale>en</defaultLocale>
<namespace>
<name locale="en">Cibic</name>
<lastChanged>2004-08-04T23:43:44</lastChanged>
<querySubject status="valid"><name locale="en">Executions</name><lastChanged>2004-08-04T23:44:32</lastChanged><definition><dbQuery><sources><dataSourceRef>[].[dataSources].[Cibic]</dataSourceRef></sources><sql type="cognos">Select <column>*</column>from<table>[Cibic].Executions</table></sql><key><name>Executionskey</name><queryItems_collection><refobj>[Cibic].[Executions].[Exec_No]</refobj></queryItems_collection></key><index><unique>true</unique><queryItems_collection><refobj>[Cibic].[Executions].[Exec_No]</refobj></queryItems_collection></index><tableType>table</tableType></dbQuery></definition><levels><level><name>Order_No</name><externalName>Order_No</externalName><key isUniqueKey="true"><name>Order_No</name><queryItems_collection><refobj>[Cibic].[Executions].[Order_No]</refobj></queryItems_collection></key><attributes/></level><level><name>Exec_No</name><externalName>Exec_No</externalName><key isUniqueKey="true"><name>Exec_No</name><queryItems_collection><refobj>[Cibic].[Executions].[Exec_No]</refobj></queryItems_collection></key><attributes/></level></levels><hierarchies><hierarchy><name>Order_No</name><externalName>Order_No</externalName><functionalDependency><refobj>Order_No</refobj><refobj>Exec_No</refobj></functionalDependency></hierarchy></hierarchies><queryItem><name locale="en">Exec_No</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_No</columnName><usage>identifier</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>count</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Order_No</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_No</columnName><usage>identifier</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>count</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Exec_Symbol</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_Symbol</columnName><usage>attribute</usage><datatype>characterLength16</datatype><precision>65</precision><size>132</size><nullable>false</nullable><regularAggregate>unsupported</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Bot_Sold</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Bot_Sold</columnName><usage>fact</usage><datatype>int32</datatype><size>2</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Exec_Qtty</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_Qtty</columnName><usage>fact</usage><datatype>decimal</datatype><precision>18</precision><scale>0</scale><size>10</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Exec_Price</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_Price</columnName><usage>fact</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Exec_Create_Date</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_Create_Date</columnName><usage>identifier</usage><datatype>dateTime</datatype><size>8</size><nullable>false</nullable><regularAggregate>unsupported</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem></querySubject><querySubject status="valid"><name locale="en">Orders</name><lastChanged>2004-08-04T23:43:43</lastChanged><definition><dbQuery><sources><dataSourceRef>[].[dataSources].[Cibic]</dataSourceRef></sources><sql type="cognos">Select <column>*</column>from<table>[Cibic].Orders</table></sql><key><name>Orderskey</name><queryItems_collection><refobj>[Cibic].[Orders].[Order_no]</refobj></queryItems_collection></key><index><unique>true</unique><queryItems_collection><refobj>[Cibic].[Orders].[Order_no]</refobj></queryItems_collection></index><tableType>table</tableType></dbQuery></definition><queryItem><name locale="en">Order_no</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_no</columnName><usage>identifier</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>count</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Buy_sell</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Buy_sell</columnName><usage>fact</usage><datatype>int32</datatype><size>2</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Symbol</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Symbol</columnName><usage>attribute</usage><datatype>characterLength16</datatype><precision>64</precision><size>130</size><nullable>false</nullable><regularAggregate>unsupported</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Order_Qtty</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_Qtty</columnName><usage>fact</usage><datatype>decimal</datatype><precision>18</precision><scale>0</scale><size>10</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Order_Price</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_Price</columnName><usage>fact</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Order_Date</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_Date</columnName><usage>identifier</usage><datatype>dateTime</datatype><size>8</size><nullable>false</nullable><regularAggregate>unsupported</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem></querySubject><relationship status="valid"><name>Orders &lt;--&gt; Executions</name><expression><refobj>[Cibic].[Orders].[Order_no]</refobj>=<refobj>[Cibic].[Executions].[Order_No]</refobj></expression><left><refobj>[Cibic].[Orders]</refobj><mincard>one</mincard><maxcard>one</maxcard></left><right><refobj>[Cibic].[Executions]</refobj><mincard>zero</mincard><maxcard>many</maxcard></right></relationship></namespace>
<dataSources><dataSource><name>Cibic</name><queryProcessing>limitedLocal</queryProcessing><cmDataSource>Cibic</cmDataSource><catalog>Cibc</catalog><schema>dbo</schema><type><queryType>relational</queryType><interface>OL</interface></type></dataSource></dataSources>
<parameterMaps>
<parameterMap hidden="true">
<name>_env</name>
<!--This reserved map supplies default values for session variables.-->
</parameterMap>
<parameterMap hidden="true">
<name>_governor</name>
<!--This reserved map specifies query governor limits.-->
</parameterMap>
</parameterMaps>
<securityViews><securityView><name>Cibc_test</name><definition><set includeRule="include"><refobj>[Cibic]</refobj></set></definition><access><decisionRole><securityObject type="group"><displayPath>Everyone [Directory &gt; Cognos]</displayPath><cmSearchPath>CAMID("::Everyone"</cmSearchPath></securityObject></decisionRole></access><functionSets><functionSetID>V_SQLServer</functionSetID></functionSets></securityView></securityViews>
<packages><package><name locale="en">Cibc_test</name><description locale="en"/><lastChanged>2004-08-04T23:45:55</lastChanged><screenTip locale="en"/><lastPublished>2004-08-04T23:45:55</lastPublished><maxVersions/><locales><locale>en</locale></locales><definition><viewref>[].[securityViews].[Cibc_test]</viewref></definition><adminAccess><decisionRole><securityObject type="role"><displayPath>Report Administrators [Directory &gt; Cognos]</displayPath><cmSearchPath>CAMID(":Report Administrators"</cmSearchPath></securityObject></decisionRole></adminAccess></package></packages>
</project>

XPath And VB
Hello Everyone!!
I Try to use function contains() of Xpath in vb and get error:
"Unknown method" and "\queryItem
ame[contains(.<--,'Exec')]
Please tell me what's wrong in this code?




The vb sentence is:

Public str_path As String
Public exitfunc As Boolean
Public SearchData As String
Public Language As String


Private Sub Command1_Click()

Dim oXml As MSXML2.DOMDocument
Dim oNodeList As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
Dim TempNode As MSXML2.IXMLDOMNode
Dim oNodeNS As MSXML2.IXMLDOMNode
Dim oNo As MSXML2.IXMLDOMNode

Dim oNodeQS As MSXML2.IXMLDOMNode
Dim root As MSXML2.IXMLDOMNode
'Dim SearchData As String
Dim par_node As String

On Error GoTo ErrHandler
Set oXml = New MSXML2.DOMDocument
Call oXml.Load("c:model.xml")
SearchData = txt_search.Text
Language = Lenguage.Text

Set oNodeList = oXml.selectNodes("//queryItem/name[contains(.,'Exec')]")
MsgBox oNodeList.length
For Each oNode In oNodeList
exitfunc = False
Call F_Get_Path(oNode, True)
Next
Exit Sub
ErrHandler:


MsgBox Err.Description

End Sub



And the xml source that I use is:



<?xml version="1.0" encoding="UTF-8" ?>
<project xmlns="http://www.developer.cognos.com/schemas/bmt/32/1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.developer.cognos.com/schemas/bmt/32/1 BMTModelSpecification.xsd">
<name>Cibc</name>
<locales>
<locale>en</locale>
</locales>
<defaultLocale>en</defaultLocale>
<namespace>
<name locale="en">Cibic</name>
<lastChanged>2004-08-04T23:43:44</lastChanged>
<querySubject status="valid"><name locale="en">Executions</name><lastChanged>2004-08-04T23:44:32</lastChanged><definition><dbQuery><sources><dataSourceRef>[].[dataSources].[Cibic]</dataSourceRef></sources><sql type="cognos">Select <column>*</column>from<table>[Cibic].Executions</table></sql><key><name>Executionskey</name><queryItems_collection><refobj>[Cibic].[Executions].[Exec_No]</refobj></queryItems_collection></key><index><unique>true</unique><queryItems_collection><refobj>[Cibic].[Executions].[Exec_No]</refobj></queryItems_collection></index><tableType>table</tableType></dbQuery></definition><levels><level><name>Order_No</name><externalName>Order_No</externalName><key isUniqueKey="true"><name>Order_No</name><queryItems_collection><refobj>[Cibic].[Executions].[Order_No]</refobj></queryItems_collection></key><attributes/></level><level><name>Exec_No</name><externalName>Exec_No</externalName><key isUniqueKey="true"><name>Exec_No</name><queryItems_collection><refobj>[Cibic].[Executions].[Exec_No]</refobj></queryItems_collection></key><attributes/></level></levels><hierarchies><hierarchy><name>Order_No</name><externalName>Order_No</externalName><functionalDependency><refobj>Order_No</refobj><refobj>Exec_No</refobj></functionalDependency></hierarchy></hierarchies><queryItem><name locale="en">Exec_No</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_No</columnName><usage>identifier</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>count</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Order_No</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_No</columnName><usage>identifier</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>count</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Exec_Symbol</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_Symbol</columnName><usage>attribute</usage><datatype>characterLength16</datatype><precision>65</precision><size>132</size><nullable>false</nullable><regularAggregate>unsupported</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Bot_Sold</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Bot_Sold</columnName><usage>fact</usage><datatype>int32</datatype><size>2</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Exec_Qtty</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_Qtty</columnName><usage>fact</usage><datatype>decimal</datatype><precision>18</precision><scale>0</scale><size>10</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Exec_Price</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_Price</columnName><usage>fact</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Exec_Create_Date</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Exec_Create_Date</columnName><usage>identifier</usage><datatype>dateTime</datatype><size>8</size><nullable>false</nullable><regularAggregate>unsupported</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem></querySubject><querySubject status="valid"><name locale="en">Orders</name><lastChanged>2004-08-04T23:43:43</lastChanged><definition><dbQuery><sources><dataSourceRef>[].[dataSources].[Cibic]</dataSourceRef></sources><sql type="cognos">Select <column>*</column>from<table>[Cibic].Orders</table></sql><key><name>Orderskey</name><queryItems_collection><refobj>[Cibic].[Orders].[Order_no]</refobj></queryItems_collection></key><index><unique>true</unique><queryItems_collection><refobj>[Cibic].[Orders].[Order_no]</refobj></queryItems_collection></index><tableType>table</tableType></dbQuery></definition><queryItem><name locale="en">Order_no</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_no</columnName><usage>identifier</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>count</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Buy_sell</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Buy_sell</columnName><usage>fact</usage><datatype>int32</datatype><size>2</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Symbol</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Symbol</columnName><usage>attribute</usage><datatype>characterLength16</datatype><precision>64</precision><size>130</size><nullable>false</nullable><regularAggregate>unsupported</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem><queryItem><name locale="en">Order_Qtty</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_Qtty</columnName><usage>fact</usage><datatype>decimal</datatype><precision>18</precision><scale>0</scale><size>10</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Order_Price</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_Price</columnName><usage>fact</usage><datatype>int32</datatype><precision>0</precision><size>4</size><nullable>false</nullable><regularAggregate>sum</regularAggregate><semiAggregate>sum</semiAggregate></queryItem><queryItem><name locale="en">Order_Date</name><lastChanged>2004-08-04T23:43:43</lastChanged><columnName>Order_Date</columnName><usage>identifier</usage><datatype>dateTime</datatype><size>8</size><nullable>false</nullable><regularAggregate>unsupported</regularAggregate><semiAggregate>unsupported</semiAggregate></queryItem></querySubject><relationship status="valid"><name>Orders &lt;--&gt; Executions</name><expression><refobj>[Cibic].[Orders].[Order_no]</refobj>=<refobj>[Cibic].[Executions].[Order_No]</refobj></expression><left><refobj>[Cibic].[Orders]</refobj><mincard>one</mincard><maxcard>one</maxcard></left><right><refobj>[Cibic].[Executions]</refobj><mincard>zero</mincard><maxcard>many</maxcard></right></relationship></namespace>
<dataSources><dataSource><name>Cibic</name><queryProcessing>limitedLocal</queryProcessing><cmDataSource>Cibic</cmDataSource><catalog>Cibc</catalog><schema>dbo</schema><type><queryType>relational</queryType><interface>OL</interface></type></dataSource></dataSources>
<parameterMaps>
<parameterMap hidden="true">
<name>_env</name>
<!--This reserved map supplies default values for session variables.-->
</parameterMap>
<parameterMap hidden="true">
<name>_governor</name>
<!--This reserved map specifies query governor limits.-->
</parameterMap>
</parameterMaps>
<securityViews><securityView><name>Cibc_test</name><definition><set includeRule="include"><refobj>[Cibic]</refobj></set></definition><access><decisionRole><securityObject type="group"><displayPath>Everyone [Directory &gt; Cognos]</displayPath><cmSearchPath>CAMID(&quot;::Everyone&quot</cmSearchPath></securityObject></decisionRole></access><functionSets><functionSetID>V_SQLServer</functionSetID></functionSets></securityView></securityViews>
<packages><package><name locale="en">Cibc_test</name><description locale="en"/><lastChanged>2004-08-04T23:45:55</lastChanged><screenTip locale="en"/><lastPublished>2004-08-04T23:45:55</lastPublished><maxVersions/><locales><locale>en</locale></locales><definition><viewref>[].[securityViews].[Cibc_test]</viewref></definition><adminAccess><decisionRole><securityObject type="role"><displayPath>Report Administrators [Directory &gt; Cognos]</displayPath><cmSearchPath>CAMID(&quot;:Report Administrators&quot</cmSearchPath></securityObject></decisionRole></adminAccess></package></packages>
</project>

XPath
I have an XML document like this...

<users>
<user>
<id>0</id>
<name>chuddy</name>
</user>
<user>
<id>1</id>
<name>chuddy1</name>
</user>
<user>
<id>2</id>
<name>chuddy2</name>
</user>
<user>
<id>3</id>
<name>chuddy3</name>
</user>
</users>

With XPath, how can I find the id that corresponds to chuddy3?

Is it:

users/user[name/text()='chuddy3'] ?

In the ASP:
xmlDoc.selectSingleNode("users/user[name/text()=''chuddy3'']" ).xml ?

Thanks!

Using XPath
Does anyone out there know how to use XPath in VB. I have my xml document stored in a DomDocument and now i would like to be able to read through it. I know how to use XPath in Xml but I don't know how to translate it over to VB. Thanks for any help

Less Than XPath
I'm using MSXML 4.0.

When I call selectNodes for all children with the MyAttribute less than "MyValue", an error occurs - 'An invalid character was found in the query string - '-&gt;&&lt;- .

MyNode.SelectNodes("*[@MyAttribute &lt; "MyValue"]")

Well this site converted AMPERSAND and lg; into the 'less than' symbol,

If I use the old &lt; , it does'nt work, like it says in the SDK help.

Thanks

Help XML Parse With XPath.
Brief Intro:
My new scanner scans a document and provides the interpreted data in an XML document. I've never worked with XML before so I looked at some XML stuff here in the forum and then did some basic tutorialing (just made that up) from the web (listed below).

Okay, I'm using this as an XPATH reference...
http://www.w3schools.com/xpath

And I also used the QueryNodes example on MSDN as a reference.
http://msdn.microsoft.com/library/de...44b9be5e6c.asp

It shouldn't be this difficult. I'm just trying to parse data that my Scanner creates in an XML file. My initial attempt is to find the number of indices in the file. As I understand it the indices element is a node and it's also an attribute. So I should be able to get it with a direct path. But when I execute the following code, sngNode is NOTHING. ???

Here's my code:

Private TestXML As DOMDocument

Private Sub Form_Load()
Set TestXML = New DOMDocument
End Sub

Private Sub ProcXML()
Dim sngNode As IXMLDOMNode
Dim rootXML As IXMLDOMNode
Dim newTxt As String
newTxt = ""
TestXML.Load "c:Test ry.xml"
Set rootXML = TestXML.documentElement
If Not rootXML Is Nothing Then
Set sngNode = rootXML.selectSingleNode("/slipresults/zone/indices")
if not sngNode is nothing then newTxt = sngNode.text
End If
dispTxt.Text = newTxt
Refresh
End Sub


Private Sub btnXML_Click()
ProcXML
End Sub

And here is my xml data:

<slipresults zones="1">
-
<zone id="1" indicies="12">
<result index="1" omrcolumn="5" omrrow="1" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="2" omrcolumn="8" omrrow="1" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="3" omrcolumn="11" omrrow="1" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="4" omrcolumn="15" omrrow="1" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="5" omrcolumn="21" omrrow="1" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="6" omrcolumn="24" omrrow="1" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="7" omrcolumn="25" omrrow="1" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="8" omrcolumn="9" omrrow="2" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="9" omrcolumn="16" omrrow="2" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="10" omrcolumn="19" omrrow="2" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="11" omrcolumn="28" omrrow="2" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
<result index="12" omrcolumn="30" omrrow="2" omrrowcount="2" omrcolumncount="30" omrconfidence="0" omrleft="0" omrright="0"/>
</zone>
</slipresults>

Any help out there???

Thanks in Advance,
Wolf

Xpath 'Like' Search
Hi,

I'm working on an application that searches through an XML document to find data. All works well for exact string but I now need to expand it to include a 'like' search (e.g. pick out a node using a search sting 'TEST' and finding a node containing 'MY TEST SEARCH' as text.

Any Ideas?

Cheers in advance.

XML XSLT XPath Example
Here are the functions for XPath.
http://www.w3schools.com/xpath/xpath_functions.asp

How come some works and some doesn't?

I attached an example of how I transfer an XML file but it fail at the current-datetime function.

Please take a look at it and tell me what you think.

Thank You

Xsd:date ?? XPATH
I'm new to XML and am facing a problem with dates ...

I have a SQL statement as follows
SELECT SW_CASE.ID , SERVICE.swDateCreated
FROMSW_CASE
INNER JOIN SERVICE ON SW_CASE.ID = SERVICE.Case_Parent
WHERE (SERVICE.swDateCreated > '2001-DEC-03') AND (SERVICE.swDateCreated < '2001-DEC-05')


and the equivalent xpath command as follows
/Case[Service/DateCreated >'2001-12-02' and Service/DateCreated <'2001-12-05']

however I am getting XML data that contains dates outside this range ... so my question really is
1>how does XML/XPATH create joins between tables
2>is there any restrictions on xpath commands for dates at a parent-child level


Any assistance will be appreciated

Thanks

Xpath Getting The Value Of An Attribute?
<items>
<item name="xyz">
 ..
 ..
</item>
</items>

I need to get xyz using xpath..

i tried as
    items/item@name
and
    items/item/@name
and it is not returning anything.. any ideas how to do it? Thanks





Edited by - getstarted on 3/15/2007 2:13:44 PM

XML XPath Search Question
I'm using Word VBA and using a background XML file to populate my list boxes. I've found having most of my hardcoded information in an XML file is must easier in error correction and in initial setup, so I'm moving that direction. My only question is how can I "search" for an attribute in a childnode? I'm thinking it would work the same way (more or less) as a database population function:


Code:
Set objNodes = objXMLDom.selectNodes("//FIRST/NODE/@optioncode")

For i = 0 to objNodes.length - 1
Set objNode = objNodes.nextNode
If Not objNode Is Nothing Then
'This is where I can't figure out how to pull the attribute text
cboList.AddItem (objNode.AttributeText)
End If
Next

The only problem is that I can't figure out how to pull the attribute value. The attribute (as shown above) is "optioncode" and hold a number. If the attribute under <NODE optioncode="10">, I need the 10 to be inserted into the combobox. What am I missing here? (just for reference, yes I do have the references to XML 3.0 referenced, not 4.0, and it's working for situations where I have to search for specific nodes. I can't find the code for the attributes).

Probably Easy XPath Question
Well, this is my code

/Main_Map/Tile[@x='5' and @y='5' and @z='0']

Its supposed to pic the node value (lets say its 4) of the tile noode with the attributes 5,5,0 (in this case)

but as I give tile 5,5,0 a child node with the value 1 I get
41 back (4 from the tiles node and 1 from the childnodes value) How to only get tiles node value, and nothing else.
Thanks (Sorry if this post is darn confuzing)

MSXML, XPATH, And Querys
Hi everybody!

I've got a list looking like this:


Code:
<variables>
<variable name="back_log" />
<variable name="basedir">MySQL Installation BaseDirectory</variable>
<variable name="binlog_cache_size" />
<variable name="bulk_insert_buffer_size" />
...
</variables>
And from visual basic using MsXml4 i'm trying to extract the value of the fields. To do this, i'm using XPATHs, but i can't quite seem to get it to work:


Code:
Set nInfo = xml.selectNodes("/variables/variable[@name='" & rs("variable_name") & "']/*")

It returns a nodelist with the length 0, and i can't extract any data at all from it. This is the first time i'm fooling around with XPATHs (i've read some about it, but never practiced it using MsXML.

How do i extract the value from within the tag based on the attribute?

Best regards,
Christopher

Need Confirmation On XPath Logic
The following link provide a set of functions for XPath.
http://www.w3schools.com/xpath/xpath_functions.asp

Are these function use only for comparing and filtering a path out? It does not return the XPath function results right?

Take the case below, is it possible to get "a1b1" or is that just use for filtering the path out?


Code:
Call sWriteOut("bookstore[concat('a1', 'b1')]")

Private Sub sWriteOut(strXPathQuery As String)
Dim objDoc As New MSXML2.DOMDocument40
If objDoc.Load("Books.xml") Then
Call objDoc.setProperty("SelectionLanguage", "XPath")
If Not objDoc Is Nothing Then
Dim objNodeList As MSXML2.IXMLDOMNodeList
Set objNodeList = objDoc.selectNodes(strXPathQuery)

If Not objNodeList Is Nothing Then
'Loop though each node in the node list
Dim objNode As MSXML2.IXMLDOMNode
For Each objNode In objNodeList
Dim XMLNod As MSXML2.IXMLDOMNode
For Each XMLNod In objNode.childNodes
Debug.Print XMLNod.baseName & ": " & XMLNod.Text
Next
Next
End If
End If
End If
End Sub


Thank You

XPath With DOMDocument40 Problems
Hi,

I am getting very frustrated with extracting information from this XML file:
CODE<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <ResponseHeader xmlns="http://healthXML.org/PasLink">
      <ErrorInformation>
        <FunctionName>GetUserLogin</FunctionName>
        <Severity>Critical</Severity>
        <ErrorType>Tehc.Data.CSL.HIS90Exception</ErrorType>
        <ModuleName>AuthenticationWS</ModuleName>
        <Description>IKKE AUTORISERT</Description>
        <Code>25</Code>
      </ErrorInformation>
      <ProfileInfo />
      <SystemInformation>
        <Version>AuthenticationWS, Version=1.14.18.23042, Culture=neutral, PublicKeyToken=null</Version>
        <SysName>PasLinkWS</SysName>
      </SystemInformation>
    </ResponseHeader>
  </soap:Header>
  <soap:Body>
    <GetUserLoginResponse xmlns="http://healthXML.org/PasLink">
      <Authentication />
    </GetUserLoginResponse>
  </soap:Body>
</soap:Envelope>

VB6 And Xpath. Trouble Getting Value Back
xml is this:-
<FORM name="PolicySelect" enabled="true">
    <COMMAND name="cmdUpload" enabled="true"/>
</FORM>

VB is this:-
'objControl is passed in and currently is a command button with the name of cmdUpload.

strControlName = objControl.Name
    
    'get xml node for control
Set objNode = objXMLDom.selectSingleNode("*[@name=" & strControlName & "]")


after this is run objNode is equal to nothing.

XPath - Using To Count Nodes??
Does anyone know how to use the XPath function to count nodes at a particular level? E.g. consider the XML below:

Code:<rareports>
    <pathnames>
        <path pathname="temp">
            <location>C:Temp</location>
        </path>
    </pathnames>
    <layers>
        <raster name="footprint">
            <folder>D:SimonTest</folder>
            <filename>livecell_ex</filename>
            <geometry>point</geometry>
        </raster>
        <shapefile name="deployment">
            <folder>D:TempShapefilesDeploymentTeams_detail.shp</folder>
            <filename>DeploymentTeams_detail</filename>
            <geometry>polygon</geometry>
        </shapefile>
        <shapefile name="servicearea">
            <folder>D:TempShapefilesServiceAreaPolygons.shp</folder>
            <filename>ServiceAreaPolygons</filename>
            <geometry>polygon</geometry>
        </shapefile>
    </layers>
</rareports>


How could I used XPath syntax to return the number of nodes equal to "shapefile"?
Cheers

XML Xpath To Return Value Of Single Node?

Hi,

does anyone have a function that if passed in the tag name i'm searching for.  ie  getNode("<Help>") that it will return the value of Help in the XML file?

Many thanks,
JD

XPath - Selected Nodes Based On Attribute Values
Hi,

My problem exists when the attribute is prefixed with a namespace.

For example, consider the following node saved in a file called test.xml.

<test xmlns="http://www.testsite.ca" xmlns:abc="http://www.abc.ca/test" identifier="XYZ"></test>

Now consider the following code (using VB 6.0 and MSXML 4.0)

Code:'*********START**************
Set XmlLoadDocument = New MSXML2.DOMDocument40
'Set namespace properties
Call XmlLoadDocument.SetProperty("SelectionNamespaces", "xmlns:testNS='http://www.testsite.ca'" )
'Load test.xml into a XML document called test
Call XmlLoadDocument.Load(test.xml)
'Use XPath to retrieve the node
Set tempNode = XmlLoadDocument.selectSingleNode("testNS:test[@testNS:abc = 'http://www.abc.ca/test']")
'*************END*************

The node tempNode is null (Nothing). No nodes are returned.

I have also tried executing,

Code:Set tempNode = XmlLoadDocument.selectSingleNode("testNS:test[@abc = 'http://www.abc.ca/test']")

and

Code:Set tempNode = XmlLoadDocument.selectSingleNode("testNS:test[@* = 'http://www.abc.ca/test']")

none of which return any nodes.

However, if I execute

Code:Set tempNode = XmlLoadDocument.selectSingleNode("testNS:test[@identifier = 'XYZ']")

then I get the proper results. A node is returned.

This leads me to believe that the problem exists when attributes are prefixed with a namespace.

Anyone know how to get past this problem????

Executing And Running A Sql Statement To Create A Query And Save Query In DataBase
I cannot seem to find the syntax needed however to execute the sql or create a query based on the SQL string that I created that can be saved. Can you please take a look at the block of code below and let me know what you think?


Sub Collect_HealthPlan()

Dim Healthplan_Count As Integer 'counts the number of items in Healthplan listbox
Dim CompanySize_Count As Integer 'counts the number of items in CompanySize listbox
Dim CurrentRow As Integer 'Stores index value of current row
Dim ctrlHPlstbox As Control 'Control variable for list box
Dim ctrlNBchkbox As Control 'Control variable for checkbox
Dim ctrlNBEAchkbox As Control 'Control variable for checkbox
Dim ctrlCompSizelstbox As Control 'Control variable for list box
Dim i As Integer
Dim j As Integer
Dim HealthPlan_Selected As Integer
'Dim ConnectDB As New ADODB.Connection
'Dim rsData As ADODB.Recordset
'Dim ADOCommand As ADODB.Command

'Set ConnectDB = Nothing
'Set ConnectDB = New ADODB.Connection

'the block of code below assigns the basic SQL upon which the form will be used to build a query
SQLString = "SELECT [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"
SQLString = SQLString & " " & "FROM [DBP 04 Pipeline Total] GROUP BY [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"

'Set dbsNorthwind = OpenDatabase("Strategic Marketing Dashboard.mdb")

'The following block of code initializes the control variables
Set ctrlHPlstbox = lstHealthPlan
Set ctrlNBchkbox = chkNB
Set ctrlNBEAchkbox = chkNBEA
Set ctrlCompSizelstbox = lstCompanySize

'The following block of code assigns the count values to variables for the list boxes (looping purposes)
Healthplan_Count = ctrlHPlstbox.ListCount
CompanySize_Count = ctrlCompSizelstbox.ListCount

i = 1

'The following block of code loops through the Healthplna list box to gather the names of all of the selected health plans
For CurrentRow = 0 To Healthplan_Count - 1
    If ctrlHPlstbox.Selected(CurrentRow) Then
        HealthPlan(i) = ctrlHPlstbox.ItemData(CurrentRow)
        'MsgBox "current item is " & HealthPlan(i), vbOKOnly, "test"
        HealthPlan_Selected = i
        i = i + 1
    End If
Next CurrentRow

i = 1

'the following block of code takes the values for all of the healthplans selected and begins building the query
'from the basic SQL Statement that was assigned to the SQLString variable. The SQL Statement in Querybuilder
'is updated dynamically based on the selections on the form
For i = 1 To HealthPlan_Selected
    QueryBuilder(i) = SQLString
    QueryBuilder(i) = QueryBuilder(i) & " HAVING ((([DBP 04 Pipeline Total].[Health Plan])=" & HealthPlan(i) & ")"
    
    If ctrlNBchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "New Business" & ")"
        
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
                
            End If
            
        Next CurrentRow
        
    End If
    
    If ctrlNBEAchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "NBEA" & ")"
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
            End If
            
        Next CurrentRow
        
    
    End If
    
Next i
    
End Sub

Let me know if you have a better way to do this..Thanks for all your help!!!

Regards,
Eric




Edited by - bahamaej on 3/3/2005 4:50:53 AM

Crystal Reports && Dynamic Query (even Tables And Fields Changes In The Query At Run Time)
 
Hi there!

I need to link the crystal report to a query that is generated dynamically. I will be knowing about which table to be linked and which fields to be retrieved at run time only. Fields are refered using an alias in all the query output to get a common name for all. I cant use a dataset or TFX file solution as mentioned earlier as i am not aware of the tables and fiels till runtime. Else i have to hardcode all the possible combinations in different tft file and link .

i tried with a temporarytable but that too doesnt seems to work.

Can anyone guide me of any simpler methode of doing this?

Bipin

Count Rows Returned By Query In The Query Strings
i have some problem guys about grid. the prob is like this. im using DBGrid n when i queried it, it return the values ryt ? but i dono how to count the data from the returned query string processing that i put in DBGrid. pls tell me the solution about this pls ? wil u help me guys ? n thx in advanced 4 ur
 help. GOD BLESS U ALL !!!

How To Add Parameters To A Query Created In Microsoft Query
I define the parameter I want (which is data placed in the excel file itself) but when I refresh the cell the sql loosses the parameter.

SSTab Query && Data Repeater Query
I have set up a SStab to make data from a system easier to organise. The system retrives the data correctly but does not update it. I am using the propertychanged method on the datarepeater.

Private Sub txtclosedate_Change()
PropertyChanged "closedate"
End Sub

Public Property Get closedate() As Date
closedate = txtclosedate
End Property

Public Property Let closedate(ByVal newclosedate As Date)
txtclosedate = newclosedate
End Property

Is the Tab affecting it if so where can I find a guide to sort this out- the KB is not that helpful.

Thanks

Frank

Query With SHAPE Command In Query Analyzer ???? Please Help!
Hi,

I am writing a complicated SQL that deals with SHAPE command.
I am using MSSQL 2000. Ok.

The problem is that Query Analyzer dont recognizes SHAPE sintaxe (gives me a "[Microsoft][ODBC SQL Server Driver] Sintaxe error or access violation" message), then is a pain-in-the-*** to keep testing the query directly in VB.

Can anyone tell me if there is some SQL environment where I can test queries with SHAPE command, or if there is any way to trick Query Analyzer to force it to understand this command?

Thanks!

Executing Query Analyzer Query File
Is it possible to execute a Query Analyzer Query File from within VB6? If so, can you show me how? Can the Query Analyzer Query File be converted into a SP?


Thanks in advance!!

Set Query Parameter For Query That Is Report Source
I am trying to assing a value to a parameter defined in one of my queries in an Access Database. The ultimate purpose is to run a report named "Summary" that has this query as the source. The problem is that I've tried different ways to assing the parameters and they don't seem to be working. How would I go about making this code work?

CODE:

Dim db As DAO.Database
Set db = CurrentDb
Dim parQ As QueryDef
Set parQ = db.QueryDefs("Summary")
Dim strGrp As String

Select Case Selection.Value 'Form value entered
Case 0
strGrp= ""
Case 1
strGrp = "One"

Case 2
strGrp = "Two"
Case 3
strGrp= "Three"
Case 4
strGrp = "Four"
End Select

parQ.Parameters("prmGrp") = strGrp
parQ.Parameters("prmSel") = lstSelect.Value 'Assign value specified in Form

DoCmd.OpenReport "Summary", acViewPreview

'This didn't work as it still prompted me to define the parameters.
'So I tried the following Code:

Dim db As DAO.Database
Set db = CurrentDb
Dim parQ As QueryDef
Set parQ = db.QueryDefs("Summary")
Dim strGrp As String
Dim rstQuery As Recordset

Select Case Selection.Value 'Form value entered
Case 0
strGrp= ""
Case 1
strGrp = "One"

Case 2
strGrp = "Two"
Case 3
strGrp= "Three"
Case 4
strGrp = "Four"
End Select

parQ.Parameters("prmGrp") = strGrp
parQ.Parameters("prmSel") = lstSelect.Value

Set rstQuery = parQ.OpenRecordset()
DoCmd.OpenReport "Summary", acViewPreview

'This doesnt work either. I have to other parameters in the query, two dates read off directly as entered in the form, that are not explicitly defined as parameters. This code gives me an error that it expected more parameters. I assume it is the dates. But the dates do have values in the form that are apparently not being read.

Any suggestions? All is appreciated.

Time Elapsed In SQL Query In VB Vrs SQL Query In Access
Hi guys,

Should it be normal for a SQL query in VB to take longer than in Access. Both queries are out to a SQL server and it is taking 30 seconds for MS Access and almost 4 minutes for VB. Is there anything I am missing or are there different engines in each of the SQL queries?

Thanks a lot,

- George

Help Needed To Create Such An SQL Query, A Looong Query?
I am trying to create one long Sql query based on users selection, but keep getting confused, need some guidance. I have created a sample access database and here is my explanation, hope i make sense!

I have three tables with the following data:
Table one: tblAssessment

UserID companyName SportName CountryName
   1 2        6         
   2 4        8    
   3 3        7
 
Table two: tblDropDown

DropDownID ParentID Name
1 0 CompanyName (as CompanyName has DropDownID=1, all the companynames will have parentId 1
2 1 Nike
3 1 Reebox
4 1 Puma
5 0 SportName (as SportName has DropDownID=5, all the Sportnames will have parentId 5)
6     5 Football
7     5 Baseball
8 5 Rugby

Table three: tblSummary

CompanyName SportName CountryName
Nike     Football England
Reebok     Baseball USA
Puma     Rugby NewZealand


Below is my brief explanation of the tables and fields

tblAssessment have 4 Fields:
UserID (PrimaryKey)
CompanyName(this stores an ID that refers to the name in the dropdown table)
SportName(This stores an ID that refers to the sportsname in the dropdown table)
CountryName

tblDropDown has three fields:
DropDownID (Primary Key)
ParentID
Name

The way tblDropDown is designed is that if the Field "Name" has data "CompanyName" and its DropDownID is "1", then
all the companyNames will have ParentID "1", so fields Nike, Reebok and Puma will have parentID "1"

tblSummary has three fields:
CompanyName
SportName
CountryName
This table has pure text data for all fields.

I need to create an sql statement that takes all the selection of a User and update the CountryName field of tblAssessment.

So for example if UserID "1", has CompanyName 2 (which is Nike, from DropDown table) and SportName 6 (which is Football from DropDown table) then lookup
for them two selection from table tblSummary and get the CountryName and update tblAssessment's CountryName Field. So in this case it will be "England" as the tblSummary
has CountryName "England" where SportName is "Football" and CompanyName is "Nike"

Could someone plz guide me how to create such a query?

I have attached a sample access database that mirrors the same above format.

Thanks,

Access Query Vs VBA Query Question
I have some code (Listing 1 below) that produces the following:

SELECT tblECOMain.JobNo, tblECOMain.ECORefNum, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, tblECOList.ManufacturedBy, tblECOList.RevisionLevel, tblECOList.SubChg, [NewQty]-[OldQty] AS Expr1, tblECOList.ECOBy, tblECOList.ECODate, tblECOList.ECOReturnProc, tblECOList.ECOReturnNo, tblECOList.ECOReturnNoReason
FROM tblECOMain INNER JOIN tblECOList ON tblECOMain.ECORefNum = tblECOList.ECORefNum
WHERE (((tblECOMain.JobNo)=99999) AND ((tblECOList.ManufacturedBy)<>'METRO MACHINE') AND ((tblECOList.SubChg)=False) AND (([NewQty]-[OldQty])<0) AND ((tblECOList.ECOReturnProc)=True));

The query runs great, however when run from the command button in VBA for Access it produces the rs.BOF or rs.EOF condition which goes to a no records to process condition. The purpose of the code is to simple check for records! I select records from a form via the "((tblECOList.ECOReturnProc)=True));" condition. If any are selected, then lets perform on those records later.

The initial form (which allows me to check my values) comes directly from a query, not code. The code basically reproduces that same query, but with the "((tblECOList.ECOReturnProc)=True));" set to true, meaning that these are the specific records I want to deal with. When I check the box, the control source is the field ECOReturnProc, for my check box control chkECOReturnProc.

I also tried referring to the control in code instead, so that the last line read (Me.chkECOReturnProc = True)); but this produced a different error. A runtime error of:

-2147217904(80040e10) No value given for one or more required parameters.

Id prefer to do this on read only, forward only since Im doing nothing to the data in this segment but have changed to open keyset, lock optimistic because Ive had similar problems before. There is no difference.

Id have to say my confusion level is on the rise. Ive done this exact thing elsewhere, in several places with no problems. I believe the code is representatively correct because the debug.print produces valid syntax. Sad to say I spent the whole day on this yesterday, and it really is something that should take 30 minutes.

Any help would be greatly appreciated!


Listing 1:
sql = "SELECT tblECOMain.JobNo, tblECOMain.ECORefNum, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, " & _
"tblECOList.ManufacturedBy, tblECOList.RevisionLevel, tblECOList.SubChg, [NewQty]-[OldQty] AS Expr1, tblECOList.ECOBy, " & _
"tblECOList.ECODate, tblECOList.ECOReturnProc, tblECOList.ECOReturnNo, tblECOList.ECOReturnNoReason " & _
"FROM tblECOMain INNER JOIN tblECOList ON tblECOMain.ECORefNum = tblECOList.ECORefNum " & _
"WHERE (((tblECOMain.JobNo)= " & [Forms]![frmReportsListing]![cboJN] & ") AND" & _
"((tblECOList.ManufacturedBy) <> " & Chr(39) & "METRO MACHINE" & Chr(39) & ") AND " & _
"((tblECOList.SubChg)=False) AND " & _
"(([NewQty]-[OldQty])<0) AND " & _
"((tblECOList.ECOReturnProc)=True));"

Debug.Print "SQLCHK: "; sql
rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.BOF Or rs.EOF Then GoTo Skip1

SQL Query From Access Query Vs Table
Hello!

Will a simple SQL query from an access query be much slower that one from a table:

SELECT * FROM qryTest (qryTest = "SELECT Name, Adress from tblTest")

vs

SELECT Name, Adresss FROM tblTest

noccy

Multitable Query In A Single Query....
Hi guys!

Long time since last time I ask for your help but now I have hit a wall... and I need your advice again...

I have 2 tables:
- Table A: Has Date field and Qty field [DateIn,Qty].
- Table B: Has Date field, Week field (1-52), Month field (1-12) [DayIn, WeekIn,MonthIn].

What I need is to retrieve the sum of Qty and show the information Daily, Weekly or Monthly.

For the daily part I have no problem:

Query = "Select DateIn, SUM(Qty) FROM Table A GROUP BY DateIn"

My problem is when I tried to do it Weekly or monthly, I don't know how to relate sum qty and GROUP by WeekIn or MonthIn in a single query.

I know I can do multiple querys using the WHERE clause "WHERE DateIn BETWEEN DayIna AND DayInB" And DayIna = First Day of week or month and DayInb = Last Day of Week or month.

Also I can Include the Week and Month fields in table A, but I don't want to have that extra information in every single row of table A.

Thanks in advance...

regards!

Sub-query, Nested Query Or A JOIN?
This should prolly go to Database forum, but there's virtually no one there at this time.

I need to pull rec's from multiple tables based on a single ID and put it all in one rec set. I can generate that query, but here's my problem...

Once I have that rec set, I need to pull another record from one of the same tables already used based on a value from that initial query and have it all in one rec set. Is that clear as mud?

I have company records coming from say 3 tables. One of the tables is an addressbook. Once I find all the related company info I need the address info for a contact name within the company info. Therefore I am trying to pull the company address and the contact's address from the addressbook table and put all the data in one rst.

Can this be done???

MySQL Query Vs Access Query
Ok so I made the switch finally after some headache but am finding query speeds are so much faster than Access. I am running into a problem with one of my Queries.

Access = #DateValue#
mySQL = 'DateValue'

Is that correct? Doug I believe you posted the wrapper for Access using the number sign, do I need to switch it back now or am I going about this wrong?

Why MSSQL Query Is Different With MYSQL Query?
somebody please help me, i have a trouble with this, i used connection to database with this

Code:


rs_xsr.Open "SELECT DISTINCT tblxsr.Mark,tblxsr.Rev FROM tblxsr where tblxsr.Transmittal='" & cmbtrans.Text & "'" & _
" and tblxsr.mark !='E' order by tblxsr.Mark,tblxsr.Number", dbcon, adOpenDynamic, adLockPessimistic


then error show this
Run Time ERRor '-2147217900 (80040e14)
[Microsoft][ODBC SQL Server Driver][SQL SErver] ORDER BY items must appears in the select list if select distinct is specified

and when i dont use "DISTINCT" that error is not show but my data is not showing, how to fix it
somebody know what the different between MYSQL query with MSSQL Query?
if u have tutorial , please send me email
harry@dbsd.co.id

Query Works In Access Query But Not Through ADO??
Hi there,
my sql string send through ADO from an asp page doesn't return any results but when I put the query directly into Access query it works fine. Got any ideas what I might be doing wrong, I'm at a loss. Below is my the query which searches between dates and the code used to connect to the db(it connects ok just doesn´t return anything). Any help would be great.

Cheers in advance
Williery

SELECT Noticias.Noticias_Fecha, Noticias.Noticias_Titulo, Noticias.Noticias_Imagene FROM Noticias WHERE (((Noticias.Noticias_Fecha) Between #10/01/2001# And #20/01/2001#) AND ((Noticias.Noticias_Titulo) Like '*test*')) AND (((Noticias.Noticias_Typo)=1));



Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("BaseDeDatosGolfNavarra.mdb")            
            
Set rsNoticias = Server.CreateObject("ADODB.Recordset")
                rsNoticias.open strSql,objConn

How To Print The Query Resukts In The Grid && Excel Row Transfer From VB Grid Query
Good afternoon. I use VB6 and have no Crytals Report. I want to print with formatting (headers plus the query) the query result which is displayed on a grid.

Also should the user want to transfer it to Excel, there is a button when you click it whatever you row you have selected onthe query results in the grid is transferred to Excel. the user may use ctrl to choose individual rows int he query grid or the use shift to select a group of rows to be transferred to Excel.

How do you use the import (to restore back up .mdb files) and export (to backup .mdb files) in real time?

Suggestions are welcomed.

God bless,
Alvin

Query For MS-Access: SQL Differences In Access Query Window Vs. Using ADO In Code
OK, many of us have come across the "Like" wildcard issue. When doing an Access query in Access itself, the "*" is used, whereas doing the query thru ADO in code, the "%" is used.

What other situations are there like this? I have a query that uses IIF, INSTR, and UCASE. When I test it in the Access query window, it works fine, but when I produce the same SQL dynamically in my VB program and try to execute it (using ADO), it chokes. Obviously, "standard" SQL doesn't like these VBA functions in the query. Does anyone know (or know where I find documentation on) the specific differences when doing queries for Access in the Access IDE vs. ADO? Thanks.

Transform Query With Sub Query
Is there any way in acccess to perform a Tranform query , and include in the where clause a Sub query?

I always get a error the the filed from the main query is not a valid field in the sub query.

Run Query Within A Query Using Code
I'm having a bit of a headache with trying to run a delete query using the results of a select query, in VB code. My first query runs OK, but when I try running the second query, I keep getting a message, "Object required."

I've enclosed my code that is causing the problem..... if y'all could take a look at it and tell me the erors of my ways, I would be very happy.

VB Code:
Dim rsMstrPN As New ADODB.Recordset    Dim rsDuplicates As New ADODB.Recordset    Dim strSQL As String    Dim strErrMsg As String    Dim lngRecCount As Long    Dim lngTotalRec As Long    Dim strPartId As String '==========================================================================' Variable Initialization Section'==========================================================================    DeletePNdata = False'==========================================================================' Code Section'==========================================================================     strSQL = ""    strSQL = strSQL & "select DuplicatePartsBad.PNID from DuplicatePartsBad "    strSQL = strSQL & "Order by DuplicatePartsBad.PNID"    rsDuplicates.Open strSQL, conJetMasterDb, adOpenStatic,    adLockReadOnly, adCmdText    If Not (rsDuplicates.EOF = True And rsDuplicates.BOF = True) Then        rsDuplicates.MoveFirst        Do While Not rsDuplicates.EOF             strPartId = rsDuplicates!PNID             strSQL = ""             strSQL = strSQL & "Delete * from MasterPN where MasterPN.PNId    = " & strPartId & "; "             Debug.Print strSQL             rsMasterPN.Open strSQL, conJetMasterDb, adOpenStatic, adLockOptimistic, adCmdText        Loop    End If        rsMstrPN.Close    rsDestination.Close


Thaks to all of you who help out this myopic programmer!!

Query On An Access Query !
I have a table with data as follows:

ProductNo, Customer
1, John
1, Simon
1, Andrew
2, Andrew
2, Dave
3, Simon
4, Pete
4, John
4, Dave

I want to display the data in a query as follows:

Product Number, Customers Bought
1, "John, Simon, Andrew"
2, "Andrew, Dave"
3, "Simon"
4, "Pete, John, Dave"


Any ideas how??????

Access Query -&gt; ADO Query Using DSN
Hello,

I have an Access database which links to tables on an MS SQL Server. It has a query which pulls together information from three different tables. Two of the tables are in one database; the third is in another. The query is supposed to grab the first full set of information it finds for a particular production unit (reel). The query is as follows, and works as expected from Access:

SELECT TOP 1 [dbo_reel].[reel_key], [dbo_Roll_Summary].[cust_key_end_user], [dbo_reel].[dt_turnup], [dbo_reel].[avg_speed_fpm], [dbo_reel].[birth_bwt_act], [dbo_reel].[birth_moisture], [dbo_Quality_Data].[Opacity_(6S20)], [dbo_Quality_Data].[TEAR_md_(2M80)], [dbo_Quality_Data].[TEAR_cd_(2C80)], [dbo_Quality_Data].[TENS_cd_(2C10)], [dbo_Quality_Data].[BRT_t_(6T10)], [dbo_Quality_Data].[SMOOTH_t_(4T40)], [dbo_Quality_Data].[SMOOTH_b_(4B40)], [dbo_Quality_Data].[POROSITY_(5S30)], [dbo_Quality_Data].[TEA_md_(2M60)]

FROM dbo_Quality_Data INNER JOIN (dbo_reel INNER JOIN dbo_Roll_Summary ON [dbo_reel].[reel_key]=[dbo_Roll_Summary].[roll_reel]) ON [dbo_Quality_Data].[sample_key]=[dbo_reel].[reel_key]

WHERE ((([dbo_reel].[reel_key])=[Reel Key]) And (([dbo_Quality_Data].[Opacity_(6S20)]) Is Not Null));

Now, I need to convert this query to a form I can use with ADO. I created a DSN for the SQL Server and pass its name to the ADODB.Connection object. I realized that the table names in Access are not those on SQL Server. Through the SQL Server database list and small test SELECT queries I found the database & table names I need and that ADO (or SQL Server?) does not like square brackets in the statement. My "converted" query is:

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user, rep_snnb.dbo.reel.dt_turnup, rep_snnb.dbo.reel.avg_speed_fpm, rep_snnb.dbo.reel.birth_bwt_act, rep_snnb.dbo.reel.birth_moisture, Technical.dbo.Quality_Data.Opacity_(6S20), Technical.dbo.Quality_Data.TEAR_md_(2M80), Technical.dbo.Quality_Data.TEAR_cd_(2C80), Technical.dbo.Quality_Data.TENS_cd_(2C10), Technical.dbo.Quality_Data.BRT_t_(6T10), Technical.dbo.Quality_Data.SMOOTH_t_(4T40), Technical.dbo.Quality_Data.SMOOTH_b_(4B40), Technical.dbo.Quality_Data.POROSITY_(5S30) , Technical.dbo.Quality_Data.TEA_md_(2M60)

FROM Technical.dbo.Quality_Data INNER JOIN (rep_snnb.dbo.reel INNER JOIN Technical.dbo.Roll_Summary ON rep_snnb.dbo.reel.reel_key=Technical.dbo.Roll_Summary.roll_reel) ON Technical.dbo.Quality_Data.sample_key = rep_snnb.dbo.reel.reel_key

WHERE (((rep_snnb.dbo.reel.reel_key)='" & reelKey & "') And ((Technical.dbo.Quality_Data.Opacity_(6S20)) Is Not Null));

So, [dbo_reel] = rep_snnb.dbo.reel; [dbo_Roll_Summary] = Technical.dbo.Roll_Summary; and [dbo_Quality_Data] = Technical.dbo.Quality_Data.

Unfortunately, the converted query doesn't work. When I run the following code, I get a "Run-time Automation" error at the last line:


   Dim rs As ADODB.recordSet
   Dim conn As ADODB.connection
   Dim sql As String

   Set rs = New ADODB.recordSet
   Set conn = New ADODB.connection
' SPNEWBERG14 = ODBC data source name
   conn.Open "SPNEWBERG14"
   sql = (query)
   rs.Open sql, conn, adOpenDynamic, adLockOptimistic


I've also tried simple JOINing queries such as this one, but still get the error.

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user FROM rep_snnb.reel INNER JOIN Technical.Roll_Summary ON rep_snnb.dbo.reel.reel_key = Technical.dbo.Roll_Summary.roll_reel WHERE rep_snnb.dbo.reel.reel_key = '5H0101';

Only the most simple SELECT queries work:

SELECT rep_snnb.dbo.reel.reel_key FROM rep_snnb.dbo.reel WHERE rep_snnb.dbo.reel.reel_key='5H0101';

Perhaps it has something to do with my JOIN statements, but they were essentially (except for the name conversion and removal of square brackets) created by Access and should work--or am I wrong in assuming that?

Thank you for any light you can shed.

- Ben

Copyright 2005-08 www.BigResource.com, All rights reserved