Xquery Return Limited Nodeset

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


ADVERTISEMENT

Xquery Return On If Node Has A Value

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

XQuery Error

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

XQuery Vs OpenRowset

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

Xquery Help SqlServer2005

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

Yukon XQuery Question

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

Where To Write XQuery Statements?

Mar 27, 2006

Where to write XQuery statements?

View 3 Replies View Related

Some XQuery/SQL Server 2005 Help Please

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

Update Query Using XQuery

Nov 27, 2006

UPDATE Sales.DeliveryScheduleSET DeliveryList.modify('declare namespacedf=http://schemas.adventure-works.com/DeliverySchedule;replace value of (df:DeliveryList/df:Delivery/df:Address)[1]' with "7194Fourth St., Rockhampton"')WHERE Sales.DeliverySchedule.ScheduleID = 1This query gives me the following errorXQuery [Sales.DeliverySchedule.DeliveryList.modify()]: Invalid sourcecharacter 0x2019Any ideas? What am I doing wrong here? Thanks for any advice.TIA,~CK

View 2 Replies View Related

XQuery Requires QUOTED_IDENTIFIER

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

Query Data From XML Column With XQuery

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

SQL 2012 :: XQuery Delete From XML Variable

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

Xquery Support In SQL-Server 2005

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

Getting All Nodes And Node Details From Xml Using Xquery

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

Xquery - Select Not Returning All Attributes / Values

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

Transact SQL :: Blank Element In XML Not Read By XQuery

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

T-SQL (SS2K8) :: XQuery Syntax To Evaluate Math Formula

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

SQL Server 2012 :: Using Xquery To Get Event Data Back From Extended Events?

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

Does SQL Server 2005 Express Support XQuery Static Type Analysis?

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

Limited Resultset Without SP

Sep 22, 2006

I have a search query on my page. However, since the resultset may be very large, I want to retreive only those results that are currently shown on the gridview page.Often a user won't browse through more than a couple of pages, so it's b-*** to send all the records to the client.I know that with a sp you can define that you want to retrieve the first or second x records, but I want to do this with SQL...is that possible?

View 1 Replies View Related

Limited User

Feb 19, 2007

I'd like to create a limited user on SQL Server 2005 to minimize any possible problems with my ASP.NET 2.0 website. My question is how limited can I make that user? What exactly does it need to be able to do?
(In case it makes a difference, my site utilizes membership, profiles, and roles, as well as some custom tables and stored procedures that I created.)
Thanks in advance for any advice! :-)

View 3 Replies View Related

What&#39;s Limited Columns That MS SQL 7.0?

Feb 19, 2001

Dear all,

When I try to add the columns to 300,
But still can not over 300 column.
What's limited columns that MS SQL 7.0?

Thank you very much..

Gemmy.

View 2 Replies View Related

Un Limited Data Storage

Aug 29, 2005

hi all,
I have a field which name is Information
and it type is Varchar (8000),but some time data access than 8000 character, my client told me,make this field to store Unlimited data.
So how can i achive this task, i m using VS 2003 (ASP.NET with VB.NET) with SQL 2000.
Thanks
Shally

View 2 Replies View Related

Users Limited Access By IP

Sep 25, 2007

Hiya - this might be a bit of a simple question but please bear with me! I have looked reasonably hard for this but can not find an answer:

I have an MSSQL 2000 server running on PC-A and would like to limit certain users (e.g. admin) to be only able to logon when using the actual PC-A machine.

I am aware that you can acheive this in MySQL with the "insert into user (host, user, password) values (localhost, username, password);" command. Is there an equivalent way to do this in MSSQL?

Thanks for your help,

Alistair.

View 1 Replies View Related

Limited User Rights

Nov 16, 2005

Hi All,

How can I give the user view rights only?

View 4 Replies View Related

DB Updates When Limited To Bak/trn Files Only

May 21, 2007

Hi,
I couldn't make a clear title without writing 2 lines.

My problem (I tried to find out through the archives):
this scenario will be used for several DBs on severals servers.

The remotes servers are not mine, I have only access to the backups files, I have no rights to setup a replication relationship.

I'm using a repository server with SQL 2005, and daily, I need to get the latest Full backup from an SQL2000 server, copy it to the repository server, restore it then delete the .bak file.
This is possible by using many different scripts (like .vbs to copy and rename the latest Full backups) then I use SQL job for daily restore.
Process is too long and the time estimated to start next task (and the backup keep growing).

Is there a way to do everything via SQL2005 script (job)?

Initially, the problem is that I have to do this with the Full backups every days (around 5 Dbs 8 Go in average). So if I can use the latest transaction log files (that would eliminate my first question).
The best way is to use log shipping as well but sql2005 is needed on both sides.

Hope I'm clear.

Thanks for any help!

View 4 Replies View Related

Limited Connections Per Login?

Jan 21, 2008

I created a login in SQL Server 2005 for SQL Server Reporting Service training purpose. It seemed the login exceed the connection limit. There were 9 people in the training and some people got the connection errors. Sometimes the connection error went away after the user re-established the connection. Is there a default limit per login? If so, what is the default? Can I change it to unlimited? And how?

Thanks.
DanYeung

View 7 Replies View Related

TRIGGER_NESTLEVEL And Limited Users

Nov 28, 2007

I want to use TRIGGER_NESTLEVEL to test if a specific trigger is on the execution stack.

Normally, the table with the trigger is accessed by stored procedures and the user does not have any direct access to the table.

I am having problems doing this because I need to use OBJECT_ID to get the ID to pass to TRIGGER_NESTLEVEL. OBJECT_ID is unable to see the table when the stored procedure is run as that limited user account.

How can I use TRIGGER_NESTLEVEL in this scenario without granting this user permissions on the table?

Here is complete code to reproduce. The output is:
Nest level is: 1 (running as admin)

Nest level is: (null) (running as limited user)

The output that I want would have a 1 instead of (null). Hard coding the specific OBJECT_ID is not the solution that I am looking for, since I want to use identical database scripts on separate installs of my database.




Code Block
IF OBJECT_ID(N'Hello') IS NOT NULL DROP TABLE Hello
IF DATABASE_PRINCIPAL_ID(N'TestUser') IS NOT NULL DROP USER TestUser
IF OBJECT_ID(N'HelloProcedure') IS NOT NULL DROP PROCEDURE HelloProcedure
GO

CREATE TABLE Hello
(
ID INT PRIMARY KEY IDENTITY,
Data INT NULL
)
GO

CREATE TRIGGER HelloTrigger1
ON Hello
FOR INSERT
AS
SET NOCOUNT ON
UPDATE Hello SET Data = 1234 WHERE ID IN (SELECT ID FROM inserted)
GO

CREATE TRIGGER HelloTrigger2
ON Hello
FOR UPDATE
AS
SET NOCOUNT ON
PRINT N'Nest level is: ' + ISNULL(CAST(TRIGGER_NESTLEVEL(OBJECT_ID(N'HelloTrigger1')) AS NVARCHAR(128)), N'(null)')
GO

CREATE PROCEDURE HelloProcedure
AS
SET NOCOUNT ON
INSERT INTO Hello VALUES (0)
GO

CREATE USER TestUser WITHOUT LOGIN
GO

GRANT EXECUTE ON HelloProcedure TO TestUser
GO

EXEC (N'HelloProcedure') -- Prints: Nest level is: 1
GO

EXEC (N'HelloProcedure') AS USER = N'TestUser' -- Prints: Nest level is: (null)
GO

View 2 Replies View Related

Limited Connections Per Login?

Jan 21, 2008

I created a login in SQL Server 2005 for SQL Server Reporting Service training purpose. It seemed the login exceed the connection limit. There were 9 people in the training and some people got the connection errors. Sometimes the connection error went away after the user re-established the connection. Is there a default limit per login? If so, what is the default? Can I change it to unlimited? And how?

Thanks.
DanYeung

View 3 Replies View Related

Report Limited To Top 8 But Not In The SQL Satement?

Mar 28, 2007

I got a report that seems to be limiting its displayed data to the first 8 records but not in the SQL statement itself. Although I have been working with SQL for many years I'm very new to Reporting Services so it may be something very simple, like perhaps a property that can be changed on the report for me to increase the amount of records the report is pulling from the database?

I don't seem to find any property set to 8 though.

Also the report is not pulling the records in the order the query pulls them, but in an apparent random order, I don't need to change this but it may help describe the report a bit better.

View 10 Replies View Related

ADO:Dispaly Limited Records

May 15, 2007

Hi all;
qrymillcgetbytype_app ( stored query in MS-Access) in my code :

MS-ACCESS Part:
query defination
PARAMETERS prmMillToolType Long;
SELECT MILLC.ID, MILLC.InsMillBdyID AS [Tool ID], MILLC.SubType, MILLC.Radius AS [End Radius], MILLC.CuttingDia AS [Tool Dia], MILLC.EffAxlCutLen AS [Effec Cut Length], MILLC.OverallLen AS [Overall Length], MILLC.HandOfCut AS [Hand Of Cut], MILLC.NoOfFlutes AS [No Of Flutes], MILLC.TmcID AS [Tool Class], MILLC.Comment, MILLC.Protrusion, MILLC.ShankDia, millC.ShoulderLen AS [Shoulder Length]
FROM MILLC
WHERE (((MILLC.[Mill Tool Type])=[prmMillToolType]) AND ((MILLC.[ON])=True))
ORDER BY MILLC.ID;



C++ PART
bstrSQL contain stored query name "qrymillcgetbytype_app"
hr = piRecordSet->put_Source(bstrSQL);
piRecordSet->Open(vNull, vNull, adOpenKeyset, adLockOptimistic, adCmdUnknown)

it opens fine
but when bstrSQL contain this query name " qrymillcgetbytype_app WHERE ID > -1 AND [Tool Dia] >= 0.000000 AND [Tool Dia] <= 5.000000 "

it displays the same output. Where clause won't work?
Pls help me out.

View 3 Replies View Related

Returning Limited Number Of Records!

Jul 8, 2004

I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.

Thanks.

View 2 Replies View Related

SQl Server Limited Privilleges User

Sep 3, 2002

Hi, I am trying to create a SQL Server user which would only have write permissions. This account would not be able to read, drop, alter, delete etc. It would simply be used to write data.

Is there a way to configure a user like this without scripting the permissions for each table. The DB has some dynamically generated tables, so the users privillges would ideally extend to those newly added tables without having to rerun some kind of script. Is this possible?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved