Yukon XQuery Question
Jul 27, 2005
I am trying to query data in an XML variable and I am having trouble
getting the data out in the format that I would like. Given the
following XML:
declare @myDoc xml
set @myDoc = '<Ownership
TeamGuid="23EA393A-3926-4A55-8329-FE747593379D" TeamName=".NET
Framework">
<Roles>
<Role Guid="93BDB4E2-95A0-438D-96E4-43032EB2BA16" Name="API Review
PM 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 get
attribute data back in normal rows and columns like the following:
Guid Name
ContactAlias
------------------------------------ ---------------------
------------
93BDB4E2-95A0-438D-96E4-43032EB2BA16 API Review PM Contact John.Smith
8A7145EF-B48B-4A93-BFA5-28B53995C022 Architect John.Doe
BF727283-B18C-415A-A38C-CEC15CD341D9 Corp VP None
I've checked the Query and Value methods and have not been able to get
them to do this for me. Is this even possible w/XQuery? Do I need to
use OpenXML to do what I am trying to accomplish?
Thanks for your help!
View 2 Replies
ADVERTISEMENT
Apr 10, 2003
If anybody willing to share the features, softwares, experience of the YUKON Beta release, please post it here or email me to mak_999@yahoo.com
Just like/want to feel the essence of YUKON.
Some links I found
http://www.microsoft.com/sql/evaluation/yukon.asp
http://advisor.com/doc/09056
http://www.eweek.com/article2/0,3959,687391,00.asp
View 3 Replies
View Related
Sep 2, 2004
Are we supposed to post/answer Yukon-related questions/answers here or there will be a dedicated forum for it?
View 3 Replies
View Related
Jan 12, 2005
Hi folks, i've installed sql 2005 express edition; after downloading the express manager and running it. i get the following error:
TO RUN THIS APPLICATION, YOU FIRST MUST INSTALL ONE OF THE FOLLOWING VERSIONS OF .NET FRAMEWORK: V2.0.40903
I've the lates version of .NET FRAMEWORK from microsoft download.
Please guide!
Howdy!
View 3 Replies
View Related
Jul 20, 2005
Hallo everyone,is there someone who is partecipating to the Microsoft Yukon Beta testand who can send me some picture of the new GUI of Enterprise Managerand Query Analyser?Send it at my own e-mail Join Bytes!ThanksLorenzo Braidi
View 5 Replies
View Related
Dec 22, 2005
Hi all,This should be an easy question to answer...I'm trying to connect to my SQL Server 2005 (running on localhost) from the code-behind of a test webpage that I made. The connection string I'm using is:Data Source=localhost;Initial Catalog=dbname;User Id=username;Password=password;Timeout=160I think the reason I'm confused is that I can't create a user in Yukon like in 2000, assigning it a username and password. In Yukon, there's no option to create a password for a user... I saw that I can create a username and password if I create an application role, so is this what I'm supposed to use?Whenever I create a user or application role and try to connect to it w/this connection string, I get:No process is on the other end of the pipe.If I make the user or application role the owner of the db_owner schema then this error message goes away but I then get:Invalid login for user ____I already made sure that I have tcp/ip and named pipes enabled for local and remote conns.Lastly, when I create a user, I have to give it a login name, which is a windows user account, so I have no idea what to put for that.So I guess my question is, what is the proper way to set up a user or application role in sql server 2005 and then be able to connect to it w/ado.net 2.0?Thanks,Justin
View 3 Replies
View Related
Nov 17, 2003
Hi Guys,
Would anyone know or have heard of any rumours if the the next version of SQL - YUKON will support 'ALTER COLUMN' without having to DROP THE SUBSCRIPTION & THEN MAKE THE CHANGES THEN RE-SYNC ???
It would be nice if i we would not have to do all that just to say .... change a column data type!!!!!
View 8 Replies
View Related
Jul 18, 2006
Hi,
Anyone kindly let me know more and clear details about the below 2 statements
1.Remove reference to undocumented system tables/stored procs
2. Remove statements modifying system objects
as are not supported in SQL Server 2005. while migrating from sql server 2000 to YUkon (sql server 2005) the above 2 points need to be taken care. but no much clarity on the above.
kindly help me
Regards
Crish
View 3 Replies
View Related
Nov 25, 2004
Hi,
I have come across this term (YUKON or something like that). Is this the 'code name' for the next version of SQL server which is yet to be launced??? Can someone help me provide links to some articles on it.
Many TIA.
View 3 Replies
View Related
Aug 19, 2004
Folks, i've just installed the express edition and have enabled all the protocols within the COMPUTER MANAGER. When i try connecting the INSTANCE through QA or EM, sql doesn't exist or access denied.
I've installed the default instance.
Please guide.
View 4 Replies
View Related
Nov 25, 2004
Hi,
I have come across this term (YUKON) or something like that and assume that this is the "code name" of thenext version of SQL server (i.e. 2005) thatis yet to belaunched. Isthis correct? If not, can someone please explain what YUKON means/stands for.
Thanks.
View 2 Replies
View Related
Dec 2, 2005
Hi yukon gurus,
View 7 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
May 25, 2005
Hello,I'm using BIDS to create a package, and I need to pass in a variable value; how does this new DTS process work, in deploying the package and passing in a custom value at runtime?Thanks.
View 2 Replies
View Related
May 21, 2004
Does anyone hava the URL?
View 2 Replies
View Related
Nov 10, 2005
Hi,
I have a need to check whether SQL Express 2005 or Yukon is installed on a machine or not.
Can anybody suggest what is right method to check the existence of these installations.
If possible suggest the registry keys which can be used for this installation check.
Thanks a lot
krishna.s
View 1 Replies
View Related
Jul 20, 2005
I did some search and didn't find anything about whether Yokon willsupport sequence object. Appreciate it if you can reply with someresources.Thanks, James
View 6 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
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
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
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
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
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
