Update Query Using XQuery
Nov 27, 2006
UPDATE Sales.DeliverySchedule
SET DeliveryList.modify('declare namespace
df=http://schemas.adventure-works.com/DeliverySchedule;
replace value of (df:DeliveryList/df:Delivery/df:Address)[1]' with "7194
Fourth St., Rockhampton"')
WHERE Sales.DeliverySchedule.ScheduleID = 1
This query gives me the following error
XQuery [Sales.DeliverySchedule.DeliveryList.modify()]: Invalid source
character 0x2019
Any ideas? What am I doing wrong here? Thanks for any advice.
TIA,
~CK
View 2 Replies
ADVERTISEMENT
Jan 22, 2013
I am trying to learn XQuery and Xpath in SQL Server. I created a sample file and uploaded it to a Table with 2 columns ID, XMLDoc. The below code is within the document in the XMLDoc column so it is the only record in the column.
I am trying to query the file so it will show all the results in a table like a normal select statement would. How would you construct the select statement to select all the information like a select * ? How would you select one field like all suppliers? I would like to select the supplier, requestor for each item.
Here is the xml:
Code:
<tst:Document xmlns:tst ="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Classification="Test" UniqueIdentifier="1234" Title="Test">
<tst:Revision RevNumber="0" TimeStamp="2013-01-21T12:56:00">
<tst:Author Name="Me" Guid="1234" />
</tst:Revision>
[Code] ....
View 1 Replies
View Related
Jan 31, 2007
Does anyone know why this works:
SELECT [MyXmlColumn].query('/root') FROM [MyDatabase].[dbo].[MyTable]
But this produces an error:
SELECT [MyDatabase].[dbo].[MyTable].[MyXmlColumn].query('/root') FROM [MyDatabase].[dbo].[MyTable]
System.Data.SqlClient.SqlException: Invalid column name 'MyXmlColumn'.
View 7 Replies
View Related
Apr 5, 2006
Please let me know the advantages and disadvantages of XQuery vsOpenRowSet in SQL Server 2005. Which would be better?Regards,Shilpa
View 1 Replies
View Related
Nov 22, 2006
I have the following XML in an XML column in a SQL 2005 Database.<DeliveryList xmlns="http://schemas.adventure-works.com/DeliverySchedule"><Delivery SalesOrderID="43659"><CustomerName>Steve Schmidt</CustomerName><Address>6126 North Sixth Street, Rockhampton</Address></Delivery><Delivery SalesOrderID="43660"><CustomerName>Tony Lopez</CustomerName><Address>6445 Cashew Street, Rockhampton</Address></Delivery></DeliveryList>I need to query that column using the value method to retreive the addressof the first delivery. I come up with this,SELECTDeliveryList.value('data((/DeliveryList/Delivery/Address)[1])','nvarchar(100)')DeliveryAddressFROM Sales.DeliverySchedulebut it tells meXQuery [Sales.DeliverySchedule.DeliveryList.value()]: There is no elementnamed 'DeliveryList'I have tried every permutation of the path and I can not seem to get it towork? Any ideas?
View 2 Replies
View Related
Sep 29, 2007
Hi,I am using xquery to extract XML fragments from my database. How do I xquery a table containing XML (typed) and return only nodes that meet the xquery.For example, the following returns empty cells if there is not @id = "op1". How I I only return values and not empty cells? SELECT xmldata.query('declare namespace ACM="www.mydomain.com"; (
for $p in //ACM:oproc[@id = "op1"]
return $p
)' ) FROM mytable Thanks
View 1 Replies
View Related
Jul 27, 2005
I am trying to query data in an XML variable and I am having troublegetting the data out in the format that I would like. Given thefollowing XML:declare @myDoc xmlset @myDoc = '<OwnershipTeamGuid="23EA393A-3926-4A55-8329-FE747593379D" TeamName=".NETFramework"><Roles><Role Guid="93BDB4E2-95A0-438D-96E4-43032EB2BA16" Name="API ReviewPM Contact" ContactAlias="John.Smith" Inherited="0" ArtifactName="" /><Role Guid="8A7145EF-B48B-4A93-BFA5-28B53995C022" Name="Architect"ContactAlias="John.Doe" Inherited="0" ArtifactName="" /><Role Guid="BF727283-B18C-415A-A38C-CEC15CD341D9" Name="Corp VP"ContactAlias="None" Inherited="0" ArtifactName="" /></Roles></Ownership>'What I would like to do is run an XQuery against the variable and getattribute data back in normal rows and columns like the following:Guid NameContactAlias------------------------------------ ---------------------------------93BDB4E2-95A0-438D-96E4-43032EB2BA16 API Review PM Contact John.Smith8A7145EF-B48B-4A93-BFA5-28B53995C022 Architect John.DoeBF727283-B18C-415A-A38C-CEC15CD341D9 Corp VP NoneI've checked the Query and Value methods and have not been able to getthem to do this for me. Is this even possible w/XQuery? Do I need touse OpenXML to do what I am trying to accomplish?Thanks for your help!
View 2 Replies
View Related
Mar 27, 2006
Where to write XQuery statements?
View 3 Replies
View Related
Nov 27, 2006
This querySELECT DeliveryList.query('declare default element namespacehttp://schemas.adventure-works.com/DeliverySchedule;<DeliveryRoute><RouteNo>{ sql:column("D.DeliveryRoute") }</RouteNo>for a$ in /DeliveryList/Delivery/Addressreturn<Address>a$</Address></DeliveryRoute>')as ResultFROM Sales.DeliverySchedule DReturns this XML<DeliveryRoute xmlns="http://schemas.adventure-works.com/DeliverySchedule"><RouteNo>3</RouteNo>for a$ in /DeliveryList/Delivery/Addressreturn<Address>a$</Address></DeliveryRoute>I need it to return this XML<DeliveryRoute xmlns="http://schemas.adventure-works.com/DeliverySchedule><RouteNo<3>/RouteNo><Address>6126 North Sixth Street, Rockhampton</Address><Address>6445 Cashew Street, Rockhampton</Address></DeiveryRoute>What am I doing wrong? Any ideas? All help is appreciated.TIA, ~CK
View 1 Replies
View Related
Dec 13, 2005
I am working for a client that would has XML data being passed into a Service Broker queue.
View 8 Replies
View Related
Dec 6, 2007
Im using the Xquery:SELECT @xmlDoc.query('
for $item in (/Collection/Content)
where $item/Html/root/DocInfo/Webinar = "White Paper"
order by $item[1]/Html[1]/root[1]/DocInfo[1]/Title[1] ascending
return $item
');
I only want to return the TOP 5 nodes (not the entire nodelist). What is the FLOWR expression or simplest way to return the limited nodeset?
Thanks
View 1 Replies
View Related
Nov 20, 2014
I'd like to delete A3 from the XML variable below, however I get "XQuery [modify()]: The XQuery syntax '/function()' is not supported."
DECLARE @XML XML = '<Attribute>
<A1>6529</A1>
<A2>6529</A2>
<A3>6529</A3>
</Attribute>'
DECLARE @n VARCHAR(100) = 'A3'
SET @XML.modify('delete (/Attribute/sql:variable("@n")[1]) ')
SELECT @XML
View 3 Replies
View Related
Jul 23, 2005
Hi,does anyone kown if the final release or one of the next Betas ofSQL-Server 2005 will support XQuery-Statemets including the LET-Feature.Thanks,Tobias
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
Jan 29, 2013
I have a table:
Code:
CREATE TABLE [dbo].[XmlTable](
[XmlId] [int] IDENTITY(1,1) NOT NULL,
[XmlDocument] [xml] NOT NULL,
CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED
[Code] .....
With a schema structure:
Code:
<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering">
<dev:Base RevisionNumber="0" Baseid="34433" />
<dev:Rev Time="2013-01-21T15:08:00">
<dev:Person Name="Me" Systemid="54654" />
</dev:Rev>
[code]....
I am trying to return the id, number, name, and location of the visitors
Something like:
Code:
RevNumber Function Id Number Visitor Location Sender
========= =========== ======== ======= ======== ======
0 A1 1 Dev01 STLRF FGY(14A)
0 A1 1 Dev02 STLRF FGY(14A)
0 A1 1 Dev03 FGRTY FGY(14A)
0 A2 1 GHFF NULL W33R
0 A2 2 UDT NULL RJ4
Here is the table insert
Code:
INSERT INTO XmlTable(XMLDocument)
SELECT * FROM OPENROWSET(
BULK 'C:Users123DesktopPractice.xml',
SINGLE_BLOB) AS x;
I Have gotten a little further, but the number is not showing for A2 and the Sender, visitor is showing null.
Code:
;WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' as dev )
SELECT
Document.value('@Title' , 'NVARCHAR(MAX)') Title,
Functions.value('@Id', 'NVARCHAR(MAX)') Functions,
A1.value('@Number', 'INT') Number,
[code]....
Results I am getting that are not all correct:
Code:
Title Functions Number VisitorName Location Sender
======== ========= ====== =========== ======== ======
Ordering A1 1 Dev01 STLRF NULL
Ordering A1 1 Dev02 STLRF NULL
Ordering A1 1 Dev03 FGRTY NULL
Ordering A2 NULL NULL NULL NULL
Not I changed Rev to Title
View 1 Replies
View Related
May 14, 2015
declare @inputXml xml
set @inputXml='<root>
<row>
<col>start_date</col>
<col>1</col>
<col>rpton#on#13-May-2015|$|rpton#on#13-May-2015</col>
[Code] ...
I get below output ....
But I need below output ....
View 7 Replies
View Related
Feb 25, 2015
I want to evaluate a math formula inside of a function, so I can't use dynamic SQL.
I found that the query on an XML-variable with a literal works well.
DECLARE @sParsedFormula varchar(200);
DECLARE @xFormula xml;
SET @xFormula = '';
SET @sParsedFormula = '1+2';
SELECT @xFormula.query('3+3') , @xFormula.query('sql:variable("@sParsedFormula")')Output (1st value is correctly evaluated, 2nd not):
"6", "1+2"
I can't directly pass @sParsedFormula to the query otherwise I get "The argument 1 of the XML data type method "query" must be a string literal". What is the correct XQuery-syntax to evaluate the SQL:variable?
View 2 Replies
View Related
Feb 25, 2014
I am trying to use xquery to get event data back from extended events. I am trying to use some sample data from Grant Fritchey but I am getting null records back. Below is the xml - I just want to retrieve a distinct list of the client_hostname and client_app_name.
<event name="login" package="sqlserver" timestamp="2014-02-19T23:53:54.299Z">
<data name="is_cached"><value>true</value></data><data name="is_dac">
<value>false</value></data><data name="database_id"><value>7</value>
</data><data name="packet_size"><value>8000</value></data><data name="options">
[Code] ....
The query I have that doesn't work is :
WITH xEvents AS
(SELECT object_name AS xEventName,
CAST (event_data AS xml) AS xEventData
FROM sys.fn_xe_file_target_read_file
('C:LoginTraceShared_0*.xel', NULL, NULL, NULL))
SELECT distinct top 1000 xEventName,
xEventData.value('(/event/data[@action_name=''Client_APP_Name'']/value)[1]','varchar') Client_APP_Name,
xEventData.value('(/event/data[@action_name=''Client_Host_Name'']/value)[1]','varchar') Client_Host_Name
FROM xEvents
View 3 Replies
View Related
Jan 1, 2007
I am quite curious about SQL Server 2005 Express, since I am conducting an XQuery implementation survey for my thesis study.
Yet I still got some questions and may need your help:
1. Does SQL Server 2005 Express support XQuery static type analysis (from http://www.w3.org/TR/xquery-semantics/)?
2. If so, since the W3C standard is still changing, how do you keep up
with the change efficiently?
Your help will be appreciated.
Happy New Year and Thank you,
Selina, Wang
View 1 Replies
View Related
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
Sep 15, 2001
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
View 1 Replies
View Related
Jul 20, 2005
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Mar 30, 2006
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies
View Related
Aug 4, 2015
After I learned the XML Schemas Collection, Using XML Data and 5 XML data type methods : query(), valuse(), exist(), modify(), and node(), I just started to do XQuery by using the Microsoft XQuery Language Reference - SQL Server 2012 Books Online and Handling Namespaces in XQuery of [URL]. I copied the following code:
-- Handlimng Namespace in Xquery (Page 6 of XQuery Languge Reference & Examples of msdn library)
-- Example A. Declaring a namespace
-- saved as MicrosoftXQueryPage6 in C:DocumentsXquery-SQLServer2012
-- 4 Aug 2015 10:55 AM
SELECT Instructions.query('
declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/AWMI:root/AWMI:Location[1]/AWMI:step
') as Result
FROM Production.ProductModel
WHERE ProductModelID=7
I executed the code in my Microsoft SQL Server 2012 Management Studio (SSMS2012). It worked nicely.
<AWMI:step xmlns:AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
Insert <AWMI:material>aluminum sheet MS-2341</AWMI:material> into the <AWMI:tool>T-85A framing tool</AWMI:tool>.
</AWMI:step>
<AWMI:step xmlns:AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
[code]....
But, I don't know what AWMI means and what the results of SELECT Instructions.query(' declare namespace... are.
View 3 Replies
View Related
Nov 19, 2004
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
Dave
View 6 Replies
View Related
Jul 10, 2006
This is my first website done in ASP.NET and SQL Server 2005. I haven't ran into any major problems, until I tried to run an update on some rows. I've searched everywhere online, only to be stuck at the end of the day. I will post the code I have for the button that triggers it. I'll be crossing my fingers! int eid = Int32.Parse(Request.QueryString["id"]);
string updateSQL = "UPDATE Events SET event_title = @event_title, event_date = @event_date, ";
updateSQL += "event_time = @event_time, event_location = @event_location, event_description = @event_description WHERE (eid = @eid)";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
cmd.CommandType = CommandType.Text;
// Add the parameters.
cmd.Parameters.AddWithValue("@event_title", txtEditTitle.Text);
cmd.Parameters.AddWithValue("@event_date", txtEditDate.Text);
cmd.Parameters.AddWithValue("@event_time", txtEditTime.Text);
cmd.Parameters.AddWithValue("@event_location", txtEditLocation.Text);
cmd.Parameters.AddWithValue("@event_description", txtEditDescription.Text);
cmd.Parameters.AddWithValue("@eid", eid);
// Try to open database and execute the update.
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
con.Close();
Response.Redirect("./?msg=eus");
}
View 11 Replies
View Related
Feb 19, 2007
Hi everyone!I have a update query which all looks good and it looks like it executes, though it does not effect the database for some unknown reason. (No error messages).protected void Button1_Click(object sender, EventArgs e)
{
int areaId = 0;
if (Request.QueryString["doc_area_id"] != null)
{
areaId = Convert.ToInt32(Request.QueryString["doc_area_id"]);
}
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CPS_docshareConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE document_area SET doc_area_name = '" + AreaText.Text + "' WHERE doc_area_id = @areaId", myConnection);
command.Parameters.Add(new SqlParameter("@areaId", areaId));
myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
} Thanks for any help you can give, cheers, Mark.
View 8 Replies
View Related
Jul 19, 2007
I am using the update query listed below in my DetailsView. The issue i am having is the ManagerDecision value which gets populated in a DropDownList in EditTemplate, doesnt seem to update correctly. If I choose 'Approved' from the DropDownList, the result for Status_Type ends up being 'Closed' and not 'Open'. Could someone please Help.
Thanks!!
UPDATE ServiceRequest_Table SET ManagerDecision = @ManagerDecision, Comments = @Comments, Priority_Name = @Priority_Name, Status_Type = CASE WHEN (ManagerDecision = 'Approved') THEN 'Open' ELSE 'Closed' END, ManagerDateTime = CASE WHEN (Status_Type = 'Closed') THEN '' END, Closed_Date = CASE WHEN (Status_Type = 'Open') THEN '' END WHERE (ServiceRequest_ID = @ServiceRequest_ID)
View 33 Replies
View Related
Jul 23, 2007
OK, so I have this page where a user can edit multiple fields in a database record. My problem is, that once changes have been made and the user hits the "save" button, everything SEEMS to run just fine (no errors, other things that should happen when the button is clicked happen normally), but nothing is changed. It's almost like the update function (which was created with Microsoft ASP.NET Web Matrix) is working, but it's being fed the old values for the page, rather than the updated ones.
Any ideas on what might be causing this? It's not like anything I've seen before.
Thanks
Kaiti
View 13 Replies
View Related
Feb 16, 2008
I gave a storedprocedure for gatting data into gridview,just go through that.
Select Ind.Industry_Name,Com.Company_Address from Pcra_Industry Ind join Pcra_Company Com on Ind.Ind_ID_PK=Com.Ind_ID_FK Here iam getting two fields from two different table depending on the Id.
So how to write a Query to change the fields in the different tables.
please Help me...
View 1 Replies
View Related
Feb 19, 2008
Im having a problem when using my update statement on button click. I want the field to be updated to be true whenever the button was clicked but it doesn't seem to like my current syntax. Can someone point me in the right direction please? I know its really simple but when you have just been looking at something for so long you can never see it!!! Thanks
SqlCommand myCommand = new SqlCommand("Update Notice SET Resolute = '" + Resolute + "', DateClosed= '" + DateClosed + "', Resolved = 'True' WHERE NoticeID = " + NoticeID + "", myConnection);
View 3 Replies
View Related
Apr 22, 2008
Hi Guys
I have a problem with UPDATE query. Here is my Select query.
SELECT d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDWhere d.EID ='1001'
According to this query I have to update another query.This is my original Update Query.
"Update Data_EMP set EStatus=0 where EID='" & TempEID & "'"
Now i wrote update query like this
UPDATE d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDSet set EStatus=0 where EID='" & TempEID & "'"
Shall i write Update query like this and Is there any issues in this update query can any one let me know?
Thanks
View 2 Replies
View Related
