Query Out An Xml Element From A Field Containing Xml Data
'message' Field Data:
<xml>
....
<test>ABC</test>
....
</xml>
How do I query out 'ABC' from this xml field data in a SQL Server?
Assume message field is in table 'Persons'
View Complete Forum Thread with Replies
Related Forum Messages:
SSIS XML Source - Distinguishing No Element From Empty Element
Our data supplier wants to be able to send us record-update information in XML, and wants to able to distinguish an empty XML element from a missing element. i.e. The following means don't change the date:- <Record> </Record> The following means set the date to NULL <Record> <ThisDate></ThisDate> </Record> Unfortunately the SSIS XML Data Flow Source sees both these as NULL. Any suggestions how I can write an SSIS package that sees these two forms as different? Thanks.
View Replies !
RDL/SRS - Is There A Way To Tag A Reference On A Data Element?
RDL/SRS - Is there a way to tag a reference on a data element? For example, let€™s say that each data element in column 5 is the result of some equation applied to the elements in columns 1 through 4. The field in column 6 is a function applied to the resulting value from column 5. Is there a way to tag a reference on the column 5 data elements, so they can be referenced from column 6 without reapplying the whole function used to derive column 5 in the first place?
View Replies !
SSIS- The Element Cannot Be Found In A Collection. This Error Happens When You Try To Retrieve An Element From A Collection On A
hi, this is sanjeev, i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container. it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error. the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package this is my code { Package pkg = new Package(); string str = (string)entry.Key; pkg.Name = str; alEntity = (ArrayList)entry.Value; ConnectionManager conMgr; Executable chPackage; TaskHost executePackageTask; Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application(); //string PackagePath = @"c:Genesis.dtsx"; //p = app.LoadPackage(PackagePath, null); p = new Package(); p.LoadFromXML(parentPackageBody, null); p.Name = str; //Sequence seqContainer; IDTSSequence seqContainer; //seqContainer = (Sequence)p.Executables["Extract Genesis Data"]; seqContainer = ((Sequence)p.Executables[0]); string packageLocation = @"Geneva Packages"; conMgr = p.Connections["SQLChildPackagesConnectionString"]; foreach (string val in alEntity) { if (seqContainer.Executables.Contains("Load_" + val) == false) { chPackage = seqContainer.Executables.Add("STOCK:ExecutePackageTask"); executePackageTask = (TaskHost)chPackage; executePackageTask.Name = "Load_" + val; executePackageTask.Description = "Execute Package Task"; executePackageTask.Properties["Connection"].SetValue(executePackageTask, conMgr.Name); executePackageTask.Properties["PackageName"].SetValue(executePackageTask, packageLocation + ddlApplication.SelectedItem.Text + @"" + executePackageTask.Name); } } app.SaveToXml(Server.MapPath("../SynchronizeScript/Packages/" + ddlApplication.SelectedItem.Text + @"") + str + ".dtsx", p, null); } please let me know what is the wrong in my code. thanks in advance. regards sanjeev bolllina sanjay.bollina@gmail.com
View Replies !
Function For Return Element List From A Query
Hello, I have do a sql function for return a list of element from a query send in variable. When I test the function on self I have no problem. But when I use the function in a sql query I have problem. example : Code SnippetSELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL, dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))') AS V_PROCESS_ITEMS FROM APPLI_SUPPLIER INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6)) This is the error : Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24 Only functions and extended stored procedures can be executed from within a function. When I do an exec of the function I have this problem : Code Snippet exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))') Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))' This is the function Code Snippet CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM] (@QUERY AS VARCHAR(3900)=null) RETURNS varchar(8000) AS BEGIN -- Insert statements for procedure here declare @v_List_ITEM as varchar(8000) set @v_List_ITEM='' if @QUERY is not null Begin declare @cur_Lect_ITEM CURSOR; declare @FUNCTION AS NVARCHAR(4000); Declare @ITEM as VARCHAR(255); SET @FUNCTION = 'set @mainCursor=cursor for ' + @QUERY + ' for read only open @mainCursor' EXEC sp_executesql @FUNCTION,N'@mainCursor cursor output', @cur_Lect_ITEM output fetch next from @cur_Lect_ITEM into @ITEM while @@fetch_status=0 begin set @v_List_ITEM=@ITEM + ' ; ' + @v_List_ITEM fetch next from @cur_Lect_ITEM into @ITEM end deallocate @cur_Lect_ITEM SET @v_List_ITEM=REPLACE(REPLACE(@v_List_ITEM, CHAR(13), ''), CHAR(10), '') set @v_List_ITEM=left(@v_List_ITEM,len(@v_List_ITEM)-3) End RETURN @v_List_ITEM END Can you help me please? Thank you
View Replies !
XML Data Integration Component - Missing Element/attribute
Hello, I use a XML Data flow item transformation in SSIS (XML Source). Here is an example of the input of xml file content:<extract date="2007-12-05"> <counters> <counter category="dispatcher" name="server1"> <runtime>6</runtime> <queue>3</queue> <maxrequest>8</maxrequest> <color>blue</color> <host> <name>svo2555</name> <path>\dispatcher</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> <counter category="gateway" name="server1"> <runtime>1</runtime> <queue>10</queue> <maxrequest>10</maxrequest> <color>purple</color> <host> <name>svo2555</name> <path>\gateway</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> </counters></extract> How do I catch the extract date in the transformation? SSIS only shows me the <counters> element and not the <extract> one. Why? Is there a way to get it?Regards,
View Replies !
Need Help Running Query On Data/time Field
I have a date/time field with a sql format of "datetime" The actualdate/time data format is MM/DD/YYYY^hh:mm:ss:pm or "1/25/200712:00:16 AM" Both the date and time are combined on the same fieldwith a space seperating the two.I need to run a query on this date/time field using the criteria oftoday's date so if I want to search all records with todays date, Ineed to query on this field. I don't need the time, but just today'sdate (MM/DD/YYYY) portion of the field.Thanks,Brian
View Replies !
Parse Data From A String Field In A SQL Query
Ok,I have a field in a database that looks something like thisField1: BLAHBLAHBLAH (10.192.168.1)I need to either extract the IP address from this field or at leastformat it to only show the IP address in the result. The problem I amhaving with my limited experience is that the BLAHBLAH piece can varyin length and of course the IP piece can vary due to different IPaddress sizes. It seems to me there should be some kind of simplesolution since the IP address is enclosed but I cant "enclose" my mindaround what I need to do.
View Replies !
Appending Data To A Text Field In Update Query?
I would like to run an update query that will update multiple rows and append a value to a text field. In theory i would want something that would function like this if possible: update table1 set field1 = field1 + 'some text to be appended' where field2 = value field1 is of datatype Text Does anyone know of a way to acomplish this? Thanks in advance!
View Replies !
Grouping By One Field And Combine Data Of Another Field In SSRS
Hi all experters, Please suggest how to build the report in below case: Raw data: ID Member Functions 1 Alan A 1 Alan B 2 Tom A 2 Tom B 2 Tom C 3 Mary D 3 Mary E Report Shows: ID Member Functions 1 Alan A,B 2 Tom A,B,C 3 Mary D,E I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID. Any good suggestion? Thanks in advance, Steve Wang 2008/4/2
View Replies !
Update SQL Field With Stripped Data From Other Field
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View Replies !
FOR XML EXPLICIT Can't Get Element 4 Going
Hello all,I'm trying to generate some XML directly from MS SQL with thefollowing codeSELECT1 AS tag, NULL AS parent, NULL AS [GoogleCustomizations!1], NULL AS [Annotations!2], NULL AS [Annotation!3], NULL AS [Annotation!3!about], NULL AS [Annotation!3!score], NULL AS [Label!4], NULL AS [Label!4!name]UNIONSELECT2 AS tag, 1 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, NULLUNIONSELECTTOP 503 AS tag, 2 AS parent, NULL, NULL, NULL, 'www.' + domainName, 1 -- score, NULL, NULLFROM tbl_auDomainNameUNIONSELECT4 AS tag, 3 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, '_cse_ad-o6lgdody'FOR XML EXPLICITThe XML it needs to create is as following<GoogleCustomizations><Annotations><Annotation about="www.clickfind.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.lookle.com/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.sensis.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>It is currently creating<GoogleCustomizations><Annotations><Annotation about="www.10000steps.org.au" score="1" /><Annotation about="www.101fm.asn.au" score="1" /><Annotation about="www.aao.gov.au" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>I cannot get my head around how I can get the label <Labelname="_cse_ad-o6lgdody" />in each element. Does anyone know?Thanks in advance.
View Replies !
Element Not Found When Deploying
Hi All, I have created 2 packages in BIDS, these are both working fine. I am trying to deploy to the server using the Package installation wizard, but I am stopped by an error stating "Could not save package" and "Element not found". Can anyone shed any light on what I need to do? Thanks in advance
View Replies !
XML Source And Global Element
Hi, I use an XML source to integrate an XML file into my db. My problem is that in my XML source component, the global element of my XML file does not appear in the Input/Output (The XSD file was generated using the same component). This global element contains attribute which I want to integrate in a table. have an idea obout how can I do that? thank you in advance
View Replies !
Matrix And Subtotal Element
Hi, I have simple report with RS 2005. I use matrix component. I€™ve added €œSubtotal€? element to the row and It works perfect. But I would like to make some modification to it and I€™m looking for expression of it, but I can€™t find it - there's just "TOTAL". Also On layout page I see text box €œTOTAL€? and that€™s all. Where can I find it? Thank you Tomasz
View Replies !
XML Source - What About The Root Element?
Greetings! Why does XML Source in MS VS 2005 Integration Services Data Flow Sources XML Source not pick up the root element? The problem is the following example: <root createddate="2008-01-01"> <elements> ... </elements </root> The XML Source picks up everything after the first element which is the root element but it does not seam to pick up the root element where there is a need for the createddate attribute. Is there a soultion for this problem or a workaround? Thanks for help.
View Replies !
Unable To Get Output Column For The Element
Hi , I am trying to load data from XML file into SQL Server database tables. I am using SSIS for this. When I am trying to do mapping in the dataflow for the source to destination, i am unable to see elements of XML in the XML Source Editor , which do not have child XML. For Example Columns option of the XML Source Editor is unable to show elements which don't have any child. I have following piece of XML file. Here following elements do not have any child elements. <EstimateDocumentID>12345</EstimateDocumentID> <ClaimNumber>12345678</ClaimNumber> <SuffixID>56789</SuffixID> Piece of XML <Reinspection Type="Request" Version="1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <EstimateDocumentID>12345</EstimateDocumentID> <ClaimNumber>12345678</ClaimNumber> <SuffixID>56789</SuffixID> <RatesInfo> <RateInfo> <RateType>BDY</RateType> <RateTierInfo> <TierNum>1</TierNum> <Rate>100.00</Rate> </RateTierInfo> </RateInfo> </RatesInfo>
View Replies !
Conditional Control Flow Element
Hi: A branching element is critical to any process flow. Currently, as far as I know, there;s only a Conditional Split Data Flow Element. There is no direct way I can branch out in a control flow. In some cases, I could branch by using a conditional operator ?: to either create a dynamic sql string for each patch, or a package name for a Package Execution task and so on. This approach is not always good enough. Are there others out there who want a Conditional Control Flow element? TIA Kar
View Replies !
Root Element Is Missing Problem
Configuration: SQL Server 2005 Enterprise edition on Microsoft Cluster. Machines named Node1, Node2 and NodeV (Virtual Node). Best practices require SSRS be deployeed on an IIS6.0 server. Reporting services was installed on the Web1 machine and pointing back to the database on the NodeV platform. Everything appears to run fine in Reporting Services. However, when I use the ReportViewer control from within a webpage, the basic control appears along with the message "root element is missing". Using the same code, I point the ReportViewer control to a different server Web2 where Sql server standard, IIS and RS are installed and the report renders properly. What am I missing in the configuration of the Web1 box as far as IIS or SSRS are concerned?
View Replies !
Element SADDR1 Is Undefined In SESSION
Hi there, I am sorry to be abother but I was wondering if there was someone to tell me what I am doing wrong. Here is my statement. I am getting the following error. Element SADDR1 is undefined in SESSION SELECT [clv].[dbo].[UNIT].[HMY] ,[clv].[dbo].[UNITTYPE].[sdesc] ,[clv].[dbo].[UNIT].[SRENT] ,[clv].[dbo].[UNIT].[dtAvailable] , clv.dbo.property.saddr1, clv.dbo.unit.scode FROM [clv].[dbo].[UNIT] inner join clv.dbo.PROPERTY on clv.dbo.property.hmy = clv.dbo.unit.hproperty inner join clv.dbo.UNITTYPE on clv.dbo.UNITTYPE.hmy = clv.dbo.unit.hunittype WHERE saddr1 = #session.saddr1# and ( hunittype='2' OR (hunittype='3') OR (hunittype='4') OR (hunittype='5') OR (hunittype='6') OR (hunittype='7') OR (hunittype='8') OR (hunittype='25') OR (hunittype='26') OR (hunittype='27') OR (hunittype='28') OR (hunittype='29') OR (hunittype='30') OR (hunittype='31') OR (hunittype='32') OR (hunittype='33') OR (hunittype='34') OR (hunittype='35') OR (hunittype='36') OR (hunittype='37') OR (hunittype='39') OR (hunittype='41') OR (hunittype='42') OR (hunittype='44') OR (hunittype='45') OR (hunittype='46') OR (hunittype='47') OR (hunittype='48') OR (hunittype='49') OR (hunittype='50') Thanks
View Replies !
Setting Collection Element Value Using Expression Fails
I'm trying to set the value of the SelectedDatabases collection property of an Update Statistics Task using an expression for the task, basing it on a user variable. Ultimately, I'll set that variable's value from a configuration file at run time and thereby specify the database whose statistics I want to update. When I set this up in the designer, I get an error at the time I try to save the package: TITLE: Microsoft Visual Studio ------------------------------ Nonfatal errors occurred while saving the package: Error at Update Statistics Task: The result of the expression "@[User::SelectedDatabase]" on property "SelectedDatabases" cannot be written to the property. The expression was evaluated, but cannot be set on the property. Error at Update Statistics Task: The result of the expression "@[User::SelectedDatabase]" on property "SelectedDatabases" cannot be written to the property. The expression was evaluated, but cannot be set on the property. ------------------------------ BUTTONS: OK ------------------------------ I used the expression editor's expression builder to select the user variable, so I assume the syntax is correct. However, I'm suspecting that there's some additional syntax required to store that value in the collection's array. I cannot find any references to any special formats and am wondering if anyone know's how to do this correctly. On the other hand, maybe it is telling me setting this property from an expression is not even allowed. If it's not allowed, why is it in the list of properties to set? In any case, none of this is clear. Any ideas or suggestions are appreciated. Thanks, Joe
View Replies !
Help On Native SSIS Xml Parser With One-many Relationships In The Same Element
Hi All, I receive data in the form of xml file and it has one to many relationships. Below I am giving you the sample node and how it should be parsed. Please suggest whether this is possible in SSIS using XML Task if not pls suggest a fast and efficient method. <Element1> <SubElement1>S1</SubElement1> <SubElement2>S2</SubElement2> .. .. <SubElement20> <Child1>c1</Child1> <Child2>c2</Child2> </SubElement20> <SubElement20> <Child1>c3</Child1> <Child2>c4</Child2> </SubElement20> .. .. <SubElement25> <SubElement25Child1>s25_One</SubElement25Child1> </SubElement25> <SubElement25> <SubElement25Child1>s25_Two</SubElement25Child1> </SubElement25> </Element1> The above one is a sample xml. I should get 8 rows for the above element. There are 2 elements with "SubElement20" and it has 2 childs each. So there are 4 such nodes. The element "SubElement25" occured 2 times. Now we need to multiply these two counts.i.e. 4 * 2 = 8. The relationship in the xml is one to many. After parsing the xml I should get the rows in the following format and it should be one to one mapping only. S1, S2, ....c1,... s25_One S1, S2, ....c1,... s25_Two S1, S2, ....c2,... s25_One S1, S2, ....c2,... s25_Two S1, S2, ....c3,... s25_One S1, S2, ....c3,... s25_Two S1, S2, ....c4,... s25_One S1, S2, ....c4,... s25_Two The options are as follows 1) Writing a custom source component to parse this xml and produces the rows in the specified way. 2) First write a XSLT or Style Sheet to break the above one to many relationships to one to one relationships and create an xml file and then use native SSIS xml parser. 3) Using the Script Task as the source component and do the parsing. But I think it would be difficult to handle and maintain. If any other suggessions please share. Thanks in advance. Regards Venkat.
View Replies !
SQL Report Designer Chart Element Transparent
Convert report from 2003 to 2005: When using Visual Studio 2003 the "Plot data as line" fill color could be set to Transparent but in Visual Studio 2005 I am not getting the same results for the column chart. Basically I want the markers without the line between the markers. Info in Visual Studio 2003 Show markers checked Plot data as line checked Fill Color is Transparent Results: Markers show but not the line between the markers. Info in Visual Studio 2005 Show markers checked Plot data as line checked Fill Color is ="Transparent" Results: Markers show and the line between the markers. If the Fill Color is Transparent get error message from the Report Designer until I add the equals sign (=) and qutoes (""). Does anyone have any ideas?
View Replies !
Code Structure For Accessing A Table Element Is VB
After i have performed a select on a table, what is the code structure to access the data of the table row element that is not bound to my form. I am using the code that is generated when i create a tableadapter. This is all new to me, so i feel very lost. I can use any help that you have. Thanks for your time.
View Replies !
[solved]Delete A MatrixRow Element On The Design Of A Report
Hi, I have a report in which a developper has put a matrix which has 4 MatrixRows. I want to delete 2 of them. When I righlt click a row and click Delete there is message about rows groups and columns groups and the row is still there. When I right click the matrix and go to properties / group, i see Row group = [Static group] and I cannot modify it. How can I delete a MatrixRow ? a+, =) -=Clement=- Configuration : SQL Server 2005 edit : I found the solution : click on the cell in the 2nd column, not in the first, then right click and delete. I would suggest Microsoft IT s to put less humor in their softs (you click delete in the wrong place, you get a weird message instead of a 'try another cell' )
View Replies !
Root Element MIssing && For Security Reasons DTD Is Prohibited In This XML Document
This error comes up in the print preview and nothing prints. We are using VS-2005, SQL 2005 and SSRS 2005. We had the same problem yesterday , so we have reinstalled IIS, .Net Framework 2.0 and Reporting Services, then reconfigured RS security, uploaded all the reports and enabled remote errors. After doing everything this error was gone and we were operating pretty fine. Suddenly just we started getting the same annoying message."ROOT ELEMENT MISSING" from Report Server and from Report Manager we have " For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method. " Please Help. I need to fix this ASAP.
View Replies !
XML Data Source Query - Receive Error On Report Execution;however Data Is Returned In Query Builder
Hello, I'm atttempting to do a test case using a Web Service to return data to a report. I'm trying to use a web based web service - http://www.webservicex.net/uszip.asmx?op=GetInfoByState€™. After finally figuring out the Query Syntax - in Query Builder I returned values. Im using an XML datasource where the connection string is: http://www.webservicex.net/uszip.asmx?op=GetInfoByState€™ The query I'm using is: <Query> <Method Namespace="http://www.webserviceX.NET" Name="GetInfoByState"> <Parameters> <Parameter Name="USState" Type="String"> </Parameter> </Parameters> </Method> <SoapAction>http://www.webserviceX.NET/GetInfoByState</SoapAction> <ElementPath IgnoreNamespaces="True">GetInfoByStateResponse{}/GetInfoByStateResult{}/NewDataSet/Table{CITY,STATE, ZIP, AREA_CODE, TIME_ZONE}</ElementPath> </Query> I then set the Parameters in the Parameter tab setting the Parameters tab of the Dataset, naming one parameter USState and setting its value to the Report Parameter (Parameters!State.Value) Like I said I launch this in the Query Builder, it prompts me for the State, and based on what State I supply it returns the information. I've then created a table to return the rows and once I launch in Preview mode I receive the following error: [rsErrorLoadingCodeModule] Error while loading code module: €˜http://www.webservicex.net/uszip.asmx?op=GetInfoByState€™. Details: Could not load file or assembly 'http://www.webservicex.net/uszip.asmx?op=GetInfoByState' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047) Any help or ideas would be appreciated! Thank You, Maureen
View Replies !
How To Convert To Regular Text, Data Stored In Image Data Type Field ????
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
View Replies !
Read XML Data From SQL Server XML Data Type Field
Hi,does anyone actually use the XML data type for SQL Server database columns? I can't really find any help for my problems.In my database there is a table with personal data such as name, telephone and so one. One of the columns is of type XML, because I need to store multilingual data in there, something like:<career> <careertext lang="en">My career</careertext> <careertext lang="de">Meine Karriere</careertext></career>I'm using a Stored Procedure to retrieve all data from the database and in my data access object there is a SQLDataReader reading results from the Stored Procedure. But how can I use something like XQuery to read specific data from this result? I thought about something like:Dim txtReader As New IO.StringReader(dr.Item("Career"))Dim myXPathDoc As New XPath.XPathDocument(txtReader)Dim myNavigator As XPath.XPathNavigator = myXPathDoc.CreateNavigator()Dim xQueryLanguage As String = "//career/careertext[@lang='en']"Dim xPNIterator As XPath.XPathNodeIterator = myNavigator.Select(xQueryLanguage)But this doesn't seem to be the right idea because xPNIterator.Current.Value always returns "My CareerMeine Karriere" What am I missing here?
View Replies !
Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table. Any help would be greatly appreciated. Current Table Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours --------------------------------------------------------------------------------------------------------- Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40 Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20 Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35 Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40 Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40 Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40 Proposed Table Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year --------------------------------------------------------------------------------------------------------------------------------------------------- Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007 Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007 Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008 Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008 Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008 Thanks, Mike Misera
View Replies !
Max Of Date Field In Query
I have a table with date fielde.g. SrNo Date 1 1-MAR-2008 2 3-Mar-2008 3 7-Mar-2008 4 10-Mar-2008 5 15-Mar-2008 I need a query to find out Max date which is less than '8-Mar-2008' . i.e i need single output '7-Mar-2008'anybody helpThanks
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
SQL DateTime Field Query
I was wondering if someone could help me here with some reporting I'm trying to do. For website visits, I currently use getdate() to have SQL insert the date and time of the visit in the table. I was wondering, is there a way to grab just the month from the field? I would like to chart this and I need to group the visits by month. I'm not sure how I would go about filtering just the month out of the entire date and time fields.
View Replies !
Empty Field Query
Hi Friends, I have one query that i have to Replace the Empty Value of a filed with some other value how can i do in SQL?? ID Phone Name 1 122 abc 2 xyz 3 444 mmm 4 525 ccc 5 nvb Now i want replace the blank (Empty) filed with some charaters Numbers how can i do that?? any Help Ashu
View Replies !
How Do I Query For Particular Criteria In Every Field In The Db?
I need to query a table for a record only if there is a certain text string in ANY OF THE TABLE'S fields. I know of using the WHERE clause with logical OR (e.g. - WHERE field1 = 'xyz' or field2 = 'xyz' or ...) but there is a limit of 40 expressions with an Access database, and I have 102 fields to look through! That's my dillema. I know I may need to go to MSSQL or Oracle, but I'd rather stay with Access. Is there a syntactical way to express the query I need? Or do I have to look somewhere other than Access for my database needs? Thanks for the advice!
View Replies !
Use Calculated Field In Same Query
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example: SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total Obviously that doesn't work, but what could I do to get that basic thing to work? Thanks!
View Replies !
SQL Query: Missing Field Value
Suppose in a table, there is some data in a field: 1, 2, 3, 5, 6, 8, 9. I need a sql query which will list the missing numbers: 4,7 (Missing digits) Query: Select * From table1 field ------ 1 2 3 5 6 8 9 Expected query:?? filed1 ------ 4 7 Please help me to get a query which will provide my expected data. Thanks. /Fakhrul(mfhossain@gmail.com)
View Replies !
Query Detail As A Field.
I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like: Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,.... From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID Where Order.OrderID=10 so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...." How can I write this query in T-SQL? Thanks
View Replies !
|
TRACKER
