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?
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!
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
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
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!!!!!
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.
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.
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.
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.
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'.
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?
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.
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
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"; (
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
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
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
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.
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
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
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?
