XML Datatype Nodes() Method
Oct 30, 2006
Dear ppl, I want to shred the following XML so that i can get all the Warranty(s) with id and text.
Required Output
id WarrantyText
1 1 year parts and labor
2 2 year parts and labor
XML
declare @myDoc xml
set @myDoc =
'<ProductDescription ProductID="1" ProductName="Road Bike">
<Features >
<Warranty id="1">1 year parts and labor</Warranty>
<Warranty id="2">2 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>'
I am using the following query but it doesn't seem to work
select C.value('@id', 'nvarchar(max)' ) as ID,
C.value('Warranty', 'nvarchar(max)' ) as WarrantyText
from @myDoc.nodes('//Warranty') T(C)
View 4 Replies
ADVERTISEMENT
Jul 24, 2015
I have a XML file (download XML File below at URL) stored in a SQL Column of XML type. The column with the XML file is named CurrentPackageXML in a table called dbo.
View 19 Replies
View Related
Oct 15, 2015
Two servers are configured with Windows 2008 / SQL server 2012 utililizing Always-On for HA. We need to upgrade both servers to Windows 2012 / SQL Server 2014 with minimum downtime(Time for Always-On failover). The upgrade to SQL 2014 is straight forward with for minimum downtime.The Windows upgrade(2008 -> 2012) is the problem. From what I have observed and read in blogs.The Windows node to be upgraded must be removed from the Windows cluster before the node can be upgraded to Win 2012.A Win 2008 and Win 2012 node can not reside in the same cluster. If this is true then the only option I can think of is to dump the DB on WIN 2008 server and restore on Win 2012. This is an outage(time it takes to dump and restore).Is there any other method to upgrade these two nodes utilizing Always-On of some other method without downtime?
View 2 Replies
View Related
May 10, 2007
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies
View Related
Jan 29, 2008
Hi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
View 7 Replies
View Related
Jan 28, 2015
Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
View 4 Replies
View Related
Apr 24, 2008
Good afternoon,
I have an issue with an ssis variable datatype.
The scenario is as follows:
I have a stored procedure:
PROCEDURE [dbo].[sp_newTransaction]
@sourceSystem varchar(50),
@txOut NUMERIC(18,0) OUTPUT
AS
insert into scn_transaction (sourceSystemName) values(@sourceSystem);
SELECT @txOut = @@identity
Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).
I execute the stored proc with the following sql with an OLE DB connection manager:
exec sp_newTransaction ?, ?
The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:
User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1
The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.
At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.
I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.
Thanks much,
B
View 6 Replies
View Related
Sep 17, 2003
Database is SQL Server 2000
I have a field in a table that stores date of birth. The field's datatype is char(6) and looks like this: 091703 (mmddyy). I want to convert this value to a datetime datatype.
What is the syntax to convert char(6) to datetime?
Thank you in advance.
View 1 Replies
View Related
Mar 14, 2008
Hi,
I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.
I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.
I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.
UPDATE users SET DATE2 = DATE.. But it also faild,..
How can I modify the column?
Thank you.
View 10 Replies
View Related
Dec 15, 2005
HI,I have a table with IDENTITY column with the datatype as INTEGER. Nowthis table record count is almost reaching its limt. that is totalrecord count is almost near to 2^31-1. It will reach the limit with inanother one or two months.In order to avoid the arithmentic overflow error 8115, we would likechange the datatype from INT to BIGINT. we hope this will solve ourproblem.How do I approch this datatype conversion?. Since the data count ishuge, that leads to a long down time of database.we need better approach or solution for this problem?. kindly give mea better solution that will reduce the total downtime of the productiondatabase.?.Regards
View 1 Replies
View Related
Feb 25, 2008
Hi guys..
i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes...
and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ?
if any article to read more about these thing,, can you refere to me...
Thanks and looking forward.-MALIK
View 5 Replies
View Related
Feb 3, 2006
Hi
i have 3 master tables 1)RoleDetails(Roleid(PK),name,masterroleid(fk) ref:RoleDetails roleid)
2) PositionDetails(positionid(PK), name,MasterPositionid(FK) ref:PostionDetails postionid,Roleid(fk) ref:Roledetails roleid)
3) Userdetails(userid(pk), loginid,pwd,roleid(fk) ref:roledetails roleid,positionid(fk)ref:postionDetails positionid,fname,address)
how to Create two functions one return child nodes as per Case 1 and another one is return Parent Nodes
as per case 2
(Manager) a -- r1 (roledetails)
/
(ROL)a1 (ROL) a2 -- r2
/ | / | |
(RO)b1 b2 b3 b4 b5 b6 -- r3
Case 1:
On passing the User ID of (a) , should get the Output as User id of ( b1,b2,b3,b4,b5,b6) along with their Role ID.
On passing the User ID of (a1), should get the Output as User ID of (b1,b2,b3)along with their Role ID.
Case 2:
On passing the Role ID of (R3), should get the User ID of all the Parent roles( a1 and a2 (R2), a(R1)long with their Role id
Case 3: on passing role id of child node , should get only all particular parent userid and roleid's.
thanks in adv.,
chakri
View 14 Replies
View Related
Aug 8, 2014
I have a situation that can be summarized like this :
DECLARE @XML XML = '<X C="0" I="1"><E D="CODE1" A="0" /><E D="CODE2" A="0.03" /><E D="CODE2" A="0.04" /></X>'
SELECT
E.value('@D','varchar(MAX)') AS Code,
E.value('@A','varchar(MAX)') AS Rate
FROM @XML.nodes('./X/E') AS T(E)
The order of appearance is of capital importance as the rates do apply on the previous ones. Is there a way to generate a sequential ID based on the order of appearance in the XML string ? In this case, I want :
1 CODE1 0
2 CODE2 0.03
3 CODE2 0.04
I though of using a temp table with Identity column... But it's not the best way for my need. I have multiple lines each with a XML String. The ROW_NUMBER() windowed function needs a ORDER BY clause that I can't provide.
View 3 Replies
View Related
Oct 26, 2005
I am looking for information on building a 4 node, multi-instance, SQL cluster on Win2K3 Datacenter server. I've consulted BOL, google, and Microsoft's "techinfo" pages for Datacenter server clustering, but everytime I think I'm going to get some good information, I end up with info on a default, 2-node cluster setup. Any guidence, links, etc. would be highly prized.
Thanks very much.
View 3 Replies
View Related
Apr 16, 2007
I have a tree structured table like the ff.
which looks like the one below in tree view.
I'd like to get the rows under a specified elementnumber which may have unlimited sub-nodes.
i.e. If i want only 'Level1' i would get results from row 2-5.
any help is greatly appreciated.
View 4 Replies
View Related
Dec 6, 2005
Dear all,I have table called CATEGORY, which is defined as follows:CREATE TABLE CATEGORY(CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINTUC__CATEGORY__CATEGORY_NAME UNIQUE,PARENT_CATEGORY_ID INTEGER,CATEGORY_ICON IMAGE,DEPTH INTEGER,CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID))Supposly, the following snap shot was taken later:================================================== ============| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID | DEPTH |================================================== ============| 1 | PC | NULL | 1 |--------------------------------------------------------------| 2 | Networks | 1 | 2 |--------------------------------------------------------------| 3 | Audio | 1 | 2 |--------------------------------------------------------------| 4 | Video | 1 | 2 |--------------------------------------------------------------| 5 | TV Cards | 4 | 3 |--------------------------------------------------------------| 6 | Graphics Cards | 4 | 3 |--------------------------------------------------------------| 7 | AGP | 6 | 4 |--------------------------------------------------------------| 8 | PCI | 6 | 4 |--------------------------------------------------------------| 9 | Input Devices | 1 | 2 |--------------------------------------------------------------and I'd like to create out of this hierarchy the following desired XMLfile:<?xml version="1.0" encoding="utf-8" ?><Hardware><Catgeory name="PC" id="1"><Catgeory name="Networks" id="2" /><Catgeory name="Audio" id="3" /><Catgeory name="Video" id="4"><Catgeory name="TV Cards" id="5" /><Catgeory name="Graphics Cards" id="6"><Catgeory name="AGP" id="7" /><Catgeory name="PCI" id="8" /></Category></Category><Catgeory name="Input Devices" id="9" /></Catgeory></Hardware>The reason for this file is that it will be a datasource of theTreeView Control new in asp.net 2.0.Now, programmateiclally using C#.net i started using the XmlDocument,XmlTextWriter and XmlTextReader Namespaces and started using susingrecurrsion to genearete this desired XML file out of the records in thesnapshot, but ...Is there an easy way of doing this using SqlServer 2005 with the newdatatype XML?*Any hint would also be ok*Best regards
View 12 Replies
View Related
Feb 7, 2008
To all the SQL H/A experts, we were wondering if we could have 3 physical nodes and 2 active/passive clusters architecture setup on a SAN as seen in the image below? http://www.geocities.com/juanlieu/CP_Arch.JPGIn case you cannot see the diagram, it would looks something like this:
active/passive Cluster A ---> physical server A (Win2003/SQL2005) ---> HP EVA SAN ---> physical server B (Win2003/SQL2005) ---> HP EVA SANactive/passive Cluster B ---> physical server B (Win2003/SQL2005) ---> HP EVA SAN ---> physical server C (Win2003/SQL2005) ---> HP EVA SAN
In this setup, I understand that Server B cannot be called upon as the active server at the SAME time by both clusters. question: what would happens if it does, would Server B reject the last cluster that calls it?Appreciated in advance.
View 3 Replies
View Related
Dec 10, 2007
Is there a way to expand the RDL to add custom nodes?
I read here there is a <Custom> element that can be put inside the <Report> node, but it does not work. I get all kinds of Scheme errors.
http://msdn2.microsoft.com/en-us/library/ms153972.aspx
I get the following errors when I add <Custom> element inside <Report>, which is acceptable according the the MSDN link above.
Deserialization failed: The element 'Report' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has invalid child element 'Custom' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'. List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinitionescription http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Author http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:AutoRefresh http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinitionataSources http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinitionataSets http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Body http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:ReportParameters http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Code http://schemas.microsoft.com/sqlserver/reporting/2005/... Line 3, position 4.
Does anyone know how to get this to work, or know where there might be some information about this online? I am coming up dry.
Thanks.
View 1 Replies
View Related
Mar 22, 2007
We have a number of databases in our company and we recently had to buy a second SQL server. What I would like to do is have both SQL Servers act as failovers for each other. They both are the exact same hardware configuration (dell blades poweredge 1855) and we have a RAID array with Melio clustered file system for our web servers. I haven€™t found any documentation on if this is even possible. Am I completely wrong in thinking this can be done or is there a better solution to spread the load but provide failover support with out buying 2 new machines?
View 1 Replies
View Related
Feb 27, 2008
Hi,
I've got quite a simple case study, I've got a database which contains N nodes and a table containing N*(N-1) rows with the distances between each two nodes (it's a symmetrical matrix), I need to do several iterations and say to which clusters and sub-cluster each of the nodes belongs to, I failed to understand if it's within the SQL SERVER capabilities to handle already known distances in order to perform the clustering algorithm (the last part of it, just combining the groups according to the principle of minimum distances within each cluster and maximum distances between the different clusters).
In addition I would also need to know the followings:
is it possible to create an iterative process which will create several levels of clustering (number of groups found), and if so how do I tell the Mining tools how many group to create?
is there an over-lapping clusters process in the SSAS? (I.E if one node belongs to several clusters who are not a complete sub-groups of each other)
Thanks!
Ron
View 8 Replies
View Related
Feb 15, 2007
I was wondering if anyone can help me out with this. Is there any way that you can have a document map in SQL Reporting Services expand on load, or if there is a way to programmatically set the Document Map nodes to be expanded when a report is generated.
Thanks in advance for your help
View 1 Replies
View Related
Nov 30, 2006
for example,xml like
'<name>test</name><age>10</age>'
select ? from @xml.nodes('/*')
how to get the node tag name like name and age?
thanks
View 4 Replies
View Related
Oct 25, 2007
Hi,
I have a wierd error occuring here.
<compilers>Line 95: <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CSharp.CSharpCodeProvider,System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" warningLevel="4" compilerOptions="/warnaserror-">Line 96: <providerOption name="CompilerVersion" value="v3.5"/></compiler>Line 97: <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" compilerOptions="/optioninfer+">Line 98: <providerOption name="CompilerVersion" value="v3.5"/></compiler></compilers></system.codedom>
Can someone help me about this one
View 2 Replies
View Related
Sep 14, 2006
We have a nested set L and R design in our database.
The design allows multiple instances of nodes in the hierarchy. Each node has a combination of node name and its instance id as the primary key. We also maintain a unique_qty column that has the unique number of nodes below a particular node. This unique qty basically ignores the multiple instances of nodes below it and counts only the distinct node names(ignoring their instance ids).
the problem that im facing is...how do i update the unique_qty when i perform any move in the tree.
UPDATE Hierarchy
SET unique_qty = ( SELECT COUNT(DISTINCT node_name) FROM Hierarchy AS H2 WHERE H2.L > Hierarchy.L AND H2.R < Hierarchy.R )
I am using the above query to find out the unique_qty when i initially populate the table.
My question is.. when i make a move of a subtree within the hierarchy, then i need to update this unique_qty for the source parents and the destination parents of the subtree( and the unique_qty for eah node in the subtree being moved remains the same ).
I had two ideas on how to update the parent nodes of the subtree:
1) for each node in the path to the root in the hierarchy, from the parent nodes( both source side and destination side) recalculate the unique_qty
2) for each node in the subtree find out until what level in the path to the root, we need to update the unique_qty, and then update only those unique_qtys
any suggestions on my methods? which one is better? any more ideas on how to do this??
View 4 Replies
View Related
May 20, 2008
Hi,
I have a SQL statement that returns a hierachy
it returns ..
-NEWSPAPER
-QUALS
- TIMES
- TELEGRAPH
-MIDS
- DAILY MAIL
- EXPRESS
- EVENING STANDARD
- POPS
- THE SUN
I want the statement to include childnumber column so for newspaper it would count the total number of children below it, so in the above example newspaper would be 9 and for the mids it would equal 3? and pops = 1. does anyone know how i can do this in SQL?
thanks for your help
View 1 Replies
View Related
Apr 8, 2006
Hi,I haev the following code:DECLARE @x xmlSET @x='<Root><row id="1"><name>Larry</name><oflw>some text</oflw></row><row id="2"><name>Joe</name></row><row id="3" /></Root>'exec sp_xml_preparedocument @idoc OUTPUT, @xSELECT * FROM OPENXML(@idoc, '/Root')This gives the following detailsidparentidnodetype localnameprefixnamespaceuridatatypeprev textI want to get the same details using XQuery, please let me know how togo about it.Regards,Shilpa
View 2 Replies
View Related
Oct 12, 2007
HI There
We had an existing 2 node active / active cluster, 1 running a default instance of Sql Server 2005 Enterprise Edition 9.0.3152 (SP2 + Hotfixes) and the other running a named instance of the same version.
We recently added 2 new nodes to the cluster, they were successfully added and we tested the cluster group failover successfuly to the new nodes.
Last night we tried to install Sql Server 2005 Enterprise edition on the new nodes.
I followed to proper proceudure of modifying the installation for both instances and selecting the 2 new nodes to apply them to. This went 100%. Sql Server 2005 successfully installed for both instances on the 2 new nodes, all log files were successful.
We then tried to apply SP2, we tried the following:
1. We ran SP2 from the active node, but when we go to the screen to select what you want to apply SP2 too we could not select anything, if you clicked on database engine the message said that these instances were already at a later version and we could not proceed. This is how i successfully applied SP2 to the original 2 node cluster but it does not work for additional nodes to an exisitng cluster.
This is also what all the documentation we could find said, refer to SP2 release notes under the topic "Failover Cluster Installation", it is also the method we found when googling.
2. We then tried what is described under SP2 release notes "Rebuild a SQL Server 2005 SP2 Failover Cluster Node".
We ran SP2 from the new nodes while they were passive, but when we got the screen where you select what to apply the SP2 too we could not select database engine the message at the bottom said that SP2 must be run from the active node and that we were attempting it from the passive node, this is what we tried in step 1 described above.
3. This was a last resort. We were advised to try failing over the instance to the new node and then running SP2. Personally i thought this was a bad idea, one should never fail over a instance of sql server to a node with incompatible binary versions and secondly when we installed sql server on the new nodes a warnng popped up before hand stating that the instances were at a later version and that the new nodes must be at this version before attempting fail over. I thought that sql would not even start, to my surprie we successfully failed over the sql group to the new node, when we ran SP2 it looked good we could select the database engine on the new node to apply SP2 too, BUT after clicking next after a few seconds the SP2 installation just closed, NO INFORMAITONAL MESSAGES NO ERRORS NO WARNINGS it just closed an never came back.
I had never seen this happen on a cluster before, needless to say this made me very nervous so we failed the sql group back to the original nodes and gave up.
PLEASE can some tell me how to apply SP2 to 2 new nodes in a 4 node cluster all methods descibed in SP2 release notes and other documentation as descibed above in step 1 and 2 do not work !
Thank You
View 3 Replies
View Related
Nov 12, 2012
 I have this query:
SELECT res.res_id,
sub.value('(./@id)[1]', 'char(2)') id
FROM vwResult res
CROSS APPLY report.nodes('/clue_personal_auto/report/search_dataset/subjects/*') report(sub)
It works just fine in SQL Query.After placing this into a view in SSDT November 2012 update, I get a compilation error.
View 11 Replies
View Related
Jul 15, 2015
I'm setting up a cluster to test a new deployments of SQL2K14 on a WSFC cluster (W2K12R2). Starwinds VS. the built in iSCSI service?Will either one allow me to build a cluster consisting of two nodes? Do I need to dedicate a third node to run the iSCSI storage?
View 4 Replies
View Related
Feb 13, 2006
I can't seem to install SQL Server 2005 (x64 version) on an x64 Win2k3 two node cluster.
I get all the way through the configuration and setup fails because it cannot start a task on the remote node. The error message indicates to check the task scheduler log file, which I have and I cannot find any 'errors' in the file.
Google/MSDN/Technet turn up nothing on multiple searches. Has anyone else run into this problem?
My installation account is a local admin on both machines, so is the sql cluster account. For the life of me I cannot figure out what's different for an x64 install vs 32bit....
View 2 Replies
View Related
Mar 13, 2008
Hello,
We currently have a 2 node Active/Passive 2 named instance SQL 2000 cluster. We will be chaning the configuration to Active/Active, basically moving 1 instance to the passive node (so we can take advantage of the resources on the passive node).
We would also like to add 3 nodes to the cluster making it a 5 node SQL cluster. What we are thinking of doing is basically making it an Active/Active/Active/Active with the 5th server being passive. The question I have is will I be able to add and install the 3 new nodes without having to redo the SQL cluster? Should I install the two new active nodes with the default SQL instace or do I have to install SQL with named instances? (actually I think the named instances is the way to go but then I wouldn't be posting here if I was sure about the answer).
Thx!
-Albert-
View 2 Replies
View Related
Nov 20, 2007
Hello,
we use SQL Server 2005 Reporting Services with XML Datasources, but we have the following issue :
with a xml like this :
<Root>
<A>a</A>
<B>b</B>
<L>Item 1</L>
<L>Item 2</L>
<C>c</C>
</Root>
If we use the query Root, the resulting dataset is :
A B C
a b c
But with this query : Root/L, the dataset is :
L A B
Item 1 a b
Item 2 a b
The dataset doesn't find the "C" node.
Anyone have an idea ?
PS : I don't control the xml structure, so I can't displace the "C" node.
View 1 Replies
View Related
Sep 14, 2006
We have a nested set design in our database.
The
design allows multiple instances of nodes in the hierarchy. Each node
has a combination of node name and its instance id as the primary key.
We also maintain a unique_qty column that has the unique number of
nodes below a particular node. This unique qty basically ignores the
multiple instances of nodes below it and counts only the distinct node
names(ignoring their instance ids).
the problem that im facing is...how do i update the unique_qty when i perform any move in the tree.
Thanks in advance
satya phani
View 4 Replies
View Related