Putting Attribute On Root Node Using FOR XML PATH

I would like to know how to put an attribute on the root node of the
xml returned from a FOR XML PATH query. One thing I tried is this:

select
m.msgid '@msgID',
st.namelong 'set/@namelong',
st.nameshort 'set/@nameshort',
from
msgset m
inner join settable st on (st.setid = m.setid)
where m.msgID = 195
for xml path('set'), root('message')

but it gives me:

<message>
<set msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
</set>
<set msgID="195">
<set namelong="MORE STUFF" nameshort="M STUFF" />
</set>
<set msgID="195">
<set namelong="TESTING 123" nameshort="TEST" />
</set>
</message>

here is what I want:

<message msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
<set namelong="MORE STUFF" nameshort="M STUFF" />
<set namelong="TESTING 123" nameshort="TEST" />
</message>

I can't get it. If I use: root(''), then it tells me: "Row tag
omission (empty row tag name) cannot be used with attribute-centric FOR
XML serialization." I'm sure there is a trick to this-- any
suggestions?

Many thanks.
the chippster

View Replies


ADVERTISEMENT

I Want To Get The Root Appliation Path For Loading A File Which Is In My Root Folder Of Windows Application

 

hi
     my application is applocation1 i kept a file in this folder i want to read it from application how can i get the path of that file in my application.
 
i want to read the file in my root folder excluding the bin path
 
Please any one know the answer let me know ..
 
thanks and regards
Ravishanker Maduri

View Replies View Related

Root Node From A FOR XML

Hello,

How can I get a XML with a root node from a FOR XML ?:
Now I have this:

The SQL:
SELECT * FROM Table_Projects Project
FOR XML AUTO

and the XML output:
<Project>
    <ItemID>1</ItemID>
    .....
</Project>
<Project>

    <ItemID>2</ItemID>

    .....

</Project>
...

and I want this:


<Projects>
    <Project>
        <ItemID>1</ItemID>
        .....
    </Project>
    <Project>
        <ItemID>2</ItemID>
        .....
    </Project>
    ...
</Projects>

thanks and regards,
Edu

View Replies View Related

Retrieve Value Of The Root Attribute In XML

Hello everyone,

I have an xml file that looks like this:

?xml version="1.0" encoding="UTF-8"?>

<WHOLE Date="*********">
<MAIN .......</MAIN>
</WHOLE>

What i need is to find a way to retrieve the value of the attribute belonging to the root element (value of @Date) in SSIS.
Tried it using Xpath but don't know how i should configure the xml task editor or if maybe there's any other way to perform such a task.

thank you

View Replies View Related

Converting Relational Data Into XML With ROOT NODE NAME In SQL 2000.

hi All,
I tried following piece of code in SQL 2005 , is working fine.
Select * from Table
FOR XML RAW('RECORDS')ELEMENTS,ROOT('MyTable').
But when i tried the same thing in SQL 2000 , it was not working .
Plz suggest a way in 2000 to get the XML output with custom RootNode Name in 2000 also like in 2005 ?
Thanks in advance.
Mohit

View Replies View Related

How To Map One Xml Node Attribute To Table Column And Other As Value

Hi All,
 
I have to do this work in ssis.
I have to read a xml and that xml have node like this

<Attribute Name="apDlBhUtil" SF="F" Value="5"/>

<Attribute Name="apDlBhUtilThresh" SF="F" Value="2"/>

<Attribute Name="apUlBhUtil" SF="F" Value="12"/>

 
I have a table which contaion column name are values of name attribute. there are 50 to 60 attribute in xml.
 
table script is
 
create table Attribute
(

apDlBhUtil int,
apDlBhUtilThresh int,
apUlBhUtil  int
)
 
now would please tell me how i can insert related value in my table.
 
my result would be like
 
apDlBhUtil apDlBhUtilThresh apUlBhUtil 

5                 2                        12
 
Thanks in advance.
Manish Jain

View Replies View Related

PMML: One Node In A Decision Tree Containing Two States Of An Attribute As The Rule For Splitting?

Hi,
is there a way to import a decision tree-model from pmml where a node contains two or more states of an attribute as the split-rule?


Example:

...
<Node recordCount="600">
   <CompoundPredicate booleanOperator="or">
        <SimplePredicate field="color" operator="equal" value="red" />
        <SimplePredicate field="color" operator="equal" value="green" />
    </CompoundPredicate>
    <ScoreDistribution value="true" recordCount="200"/>
    <ScoreDistribution value="false" recordCount="400"/>
</Node>
...

This node shoud contain all cases, whose color is red or green (The Microsoft DecisionTree-Algorithm would build a model with two steps like red/ not red and then green / not green). According to the DMG, this is valid PMML 2.1, but when trying to import the server complains about an unexpected value in the SimplePredicate-tag.

How can i import such a node in SqlServer 2005?

Thank you in advance for any help

Chris

View Replies View Related

How Do I Write A Query To Get The Path From Ancestor To Node In A Tree?

Hi all. Here's my problem: I have a tree linking macaddresses (bigints)in a tree structure. i want to get the path from node a to b.create table tree1(father bigint , child bigint);insert into tree1 (father,child) values (100,200);insert into tree1 (father,child) values (100,300);insert into tree1 (father,child) values (100,400);insert into tree1 (father,child) values (200,2000);insert into tree1 (father,child) values (200,3000);insert into tree1 (father,child) values (100,4000);insert into tree1 (father,child) values (2000,11111);you can see that 100 --> 200 --> 2000 --> 11111select * from tree1what i would like is a query that given two parameters returns the pathbetweenthem, in the case of 100,11111 i want to get100200200011111if possible as different rows, but columns will do to.of course i do not know the legnth of the path. it can be very bigthx in advanceTzvika

View Replies View Related

Measure Group Attribute Key Column Does Not Match Source Attribute

 

HI,

 
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
 
Measure group attribute key column x does not match source attribute ..
 
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
 
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
 
Is this a flaw in the BIDS or I should be missing something.
 
thanks
MJ

View Replies View Related

Do I Need To Install SQL Server To Second Node In Cluster Prior To Adding The Node?

I read these instructions:

http://msdn2.microsoft.com/en-us/library/ms191545(SQL.90).aspx

 

But I'm not sure if I have to install SQL Server first on node 2, then add it to the cluster. Or does adding it to the cluster also install the software?

 

Thanks

View Replies View Related

Transfering Cluster Resources From One Node To Another Node

 
 
 I configure Windows 2003 R2 and SQL 2005 two nodes Cluster. When I move cluster resource from one node to anther node it takes around 30 seconds to become online. So in that time if any query is running it stops responding.
 
 
 
So please suggest in this regard

View Replies View Related

Subject: BCM Install Error - Logfile &&amp; SQL Path Path &&amp; MSSQL.1?

When trying to install Business Contact Manager (BCM) for Outlook 2007, the setup failed and I was refered to a log file in my Local Settings/Temp folder. The log actually says that Business Contact Manager was installed sucessfully! BCM is supposed to install SQL Express 2005 as an instance or as instance if SQL Express is already installed. There is an MSSMLBIZ instance in Services.. 
 
Who can I send the Log File to for analysis and the fix feedback?
 
When I first went into Computer Management and clicked on Services and Applications in the left panel, the error message appeared "Snap-in failed to intialize. Name: SQL Server Configuration Manager CLSID:{CA9F8727-31DF-41D2-975C-887D84903967} This message diappeared when I clicked on Services and Applications again. Under Services, there are 3 SQL services - one is an application that was uninstalled 3-4 weeks ago and I disabled this service. The other 2 are: SQL Server (MSSMLBIZ) and the other one is SQL Server (SQLEXPRESS) When I tried to start either of the last 2, the message appeared: Services "Could not start the SQL Server (MSSMLBIZ) service on Local Computer. Error 3: The system cannot find the path specified. Under Program Files/Microsoft SQL Server/MSSGL.1 folder is mostly empty. So, it seems like the Path in the Registry is not valid and that nothing is being installed in the MSSQL.1 folder. If so, how do I fix this?
 
How do I get the BCM SQL instance to install and run properly? what do the messages in Services mean and how do I resolve these.
 
Thank you!
 
Gary 

View Replies View Related

SQL Server Not Starting - Tempdb Path Updated To Wrong Path

After updating TempDB path to a wrong path (without file name only folder name) the service is not starting. How can i sovle this and start the service
 
thanks
 
Leena

View Replies View Related

How Do You Determine The &&"active&&" Node Inside A Stored Proc For An Active/passive 2-node Cluster?

I invoke  xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster.  Depending on which server the xp_cmdshell gets executed on I need to pass different arguments  in the shell command.  I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example I know my active node is server2, but the host_name() function is returning server1.  The only thing that I could possible explain this is that the MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes).  Does the xp_cmdshell get executed by the SQL active node or the MDTC active node?  And what is the best way to find out which server is going to run my xp_cmdshell?
 
Thanks.
 
Edit:
 
Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on.  On server1 I get server1 and on server 2 I get server 2, all the while server2 is the active node. I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...
 
Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.

View Replies View Related

Drill Through Link Produces Error: The Path Of The Item &&"(null)&&" Is Not Valid. The Path Must Be Less Than 260 Characters Long An

I have a jump-to-report link in a text box that successfully goes to another report.  The second report has a similar link to the first report.  When it is clicked, the following error results.. 





The path of the item "(null)" is not valid. The path must be less than 260 characters long and must start with slash. Other restrictions apply. (rsInvalidItemPath) 



I have not been able to find a difference in the way the links were defined, they look consistent.  Both reports have the same permissions, etc. 

 

Please advise

View Replies View Related

FOR XML Sub Root

Hi all
I am trying to create xml file from the data, everything works fine cept I can't get the format that I need.

This is my SP:

WITH XMLNAMESPACES (
'urn:http://base.google.com/cns/1.0' as "c"
, 'urn:http://base.google.com/ns/1.0' as "g"
)

SELECT top 2
TITLE "item/title",
description "item/description",
employer "item/g:employer",
job_type "item/g:job_type",
link "item/link",
location "item/g:location",
salary "item/g:salary",
education "item/g:education"


FROM JobExport
FOR XML PATH ('chanel'), ROOT('rss')

I need to get:

<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0" xmlns:c="http://base.google.com/cns/1.0">
- <channel>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education></g:education>
</item>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education>High School</g:education>
</item>
</channel>
</rss>

but I get this:


<rss xmlns:g="urn:http://base.google.com/ns/1.0" xmlns:c="urn:http://base.google.com/cns/1.0">
<chanel>
<item></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
<g:education></g:education>
</item>
</chanel>
<chanel>
<item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
</item>
</chanel>
</rss>

The problem is that the "chanel" need to be a subroot (not sure what else to call it)

View Replies View Related

Different Login && SQL Console Root .....

Logged on local Administrator on one of my sqlserver 2000 "ABC" and registered 5 sql groups and linked 50 sql2k servers from MSDE to Standare (all sp3).
Now, I need a different login on this machine "ABC", how could I get back all the SQL Enterprise Manager Console Root Setting and connections like before?

thanks
David

View Replies View Related

XML Source - What About The Root Element?

Greetings!

Why does XML Source in MS VS 2005 Integration Services Data Flow Sources XML Source not pick up the root element?

The problem is the following example:
<root createddate="2008-01-01">
<elements>
...
</elements
</root>

The XML Source picks up everything after the first element which is the root element but it does not seam to pick up the root element where there is a need for the createddate attribute.

Is there a soultion for this problem or a workaround?

Thanks for help.

View Replies View Related

How To Change The Root Directory?

Hello,

 

I was wondering if it were possible to change the Root Directory on an SQL Server 2005 install?

 

expl: current = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL

 

desired: D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL

View Replies View Related

Master Database On Root Drive??

I have a 'need an opinion' question. I've always installed the system databases (master, msdb, model, tempdb) on the root drive (c:) with the sql application. Then I was thinking, the only things that really should be on the root drive is the sql app itself (and any other required apps). So...my question is, do y'all install the master, etc on a separate drive other than the root, ie d: or e:? The reason I ask is that if the tempdb database has alot of activity, and the database resides on the root drive, could it affect sql performance?

Just wondering. I just started working in a 'virgin' sql environment (setting up sql servers from scratch), and want to have sql installed in the best possible way for performance.

Thanks in advance for any info y'all can provide.

View Replies View Related

Root Directory Of SSIS Package

Hi everyone!
I have a configuration file, I want to place it in somewhere of SSIS package to extract configuration information. But i dont know where is root directory. can you help me?
Thanks you very much

View Replies View Related

Does Anyone Know How To Change The Document Map Root Text?

hi, guys
Does anyone know how to change the Document Map root text? For example, i have report, the file name is sc.rdl, and then the root is sc. Apparently this is not good. I am thinking is there a way to change it.

View Replies View Related

Root Element Is Missing Problem

Configuration:  SQL Server 2005 Enterprise edition on Microsoft Cluster.  Machines named Node1, Node2 and NodeV (Virtual Node).  Best practices require SSRS be deployeed on an IIS6.0 server.  Reporting services was installed on the Web1 machine and pointing back to the database on the NodeV platform.  Everything appears to run fine in Reporting Services.  However, when I use the ReportViewer control from within a webpage, the basic control appears along with the message "root element is missing".  Using the same code, I point the ReportViewer control to a different server Web2 where Sql server standard, IIS and RS are installed and the report renders properly.  What am I missing in the configuration of the Web1 box as far as IIS or SSRS are concerned?
 
 

View Replies View Related

How Can I Directly Include A Square Root In A SQL Query

I have an ASP.NET application where I need to filter the records returned from a SQL query using a calculated distance to a point that is set by the user.   To do this directly, I would need to include a square root of an expression in the SQL query.  It seems like I should be able to do this by writing my SQL query something like:
SELECT SQRT(expression ....) AS distance, column2, column3 ... FROM mytable WHERE distance < 50
Unfortunately, SQL queries apparently don't like the SQRT function.  I am using .NET 2.0 with VS2005 and a MS Access backend (that will soon be moved to SQL/Server).
As an alternate, maybe I can query the data into a temporary record set with a extra field for distance and then step my way through the temporary record set and replace the values in the distance field with a calculated distance.  Once the temporary record set is prepared, then I would delete the records that don't pass the distance requirement or otherwise hide them and bind the data to a gridview control.
Thanks,
David

View Replies View Related

INTERNAL ERROR Com_check Root->right Before Compile

Could someone tell me what "[INTERNAL ERROR] com_check root->right before compile" means, I get it in the SQL error log. It comes right after "Unable to recognize network login packets" (also get lot if these on another server). I think I know what the "Unable to recognize login packets" error means, but I am unable to get any information on the INTERNAL Error problem. HELP! Oh by the way, we are running MSSQL Server 6.5 on NT 4.0, it has been upgraded with service pack 5. Also, the front end application is called MP2 for the maintenance department to track tasks. All of the users are either logged in through PC to server or intranet to server.

View Replies View Related

How To Move Server Root Directory To Another Partition?

I want to move the SQL Server 7.0 root directory from C:MSSQL7 to D:MSSQL7 (same host). I see the startup parameters under server properties could be changed to point to the new location, but Master has the paths to all the databases recorded in sysdatabases table, so at least that would have to change, and sysdevices table points to files on C:. And maybe the NT registry points to some files there too. Anyway, is there some established method to move it? If that isn't practical, at least I would want to move TEMPDB ( perhaps by using the SP_DETACH and SP_ATTACH method ). My main database is already on D: so I do not have to move it. Any informed advice would be appreciated. Thanks.

View Replies View Related

Using Application.ExistsOnDtsServer, Root Folder Unknown

Hi... I'm using an object of type Microsoft.SqlServer.Dts.Runtime.Application and I want to use the  ExistsOnDtsServer method

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.existsondtsserver.aspx

I am loading an MSDB-related SSIS package located in the root SSIS folder.
Originially, the root MSDB folder was named "MSDB", so this call would work...

Dim app As New Microsoft.SqlServer.Dts.Runtime.Application

blnReturn = app.ExistsOnDtsServer("MSDB" & Me.PackageFolderPath & "" & Me.PackageName_SSMS, Me.Datasource)

but now, the "MSDB" folder has been renamed to "MSDB - <ServerName>", which the same code above now bombs.

I could change the code above to insert the servername, but I'd like to find something more automated to work for a root folder of any pattern.

Is anyone aware of a way to pull the root folder name through T-SQL, or some sort of wildcard character that bypasses specifics?

blnReturn = app.ExistsOnDtsServer("*" & Me.PackageFolderPath & "" & Me.PackageName_SSMS, Me.Datasource)

View Replies View Related

Remove ROOT Tags From SQL Task XML Results

Hi All,

I'm currently using an Execute SQL Task to return XML data from a query into an SSIS string variable.  In my FOR XML clause in SQL I'm specifying a certain name for my root tag, called "Accounts".  This works great in Management Studio, however, the Execute SQL Task appends a <ROOT> and </ROOT> tag to the start and end of the string, so now it looks like:

<ROOT><Accounts>...all my elements...</Accounts></ROOT>

I'd like to remove the ROOT tags so that the <Accounts> tags are actually the root for this doc.  What would be the best way to remove the ROOT tags from the SSIS string variable?

Thanks!

View Replies View Related

Need Help In Pulling Out A Date In The Root Portion Of An XML File

Below is a file that I have loaded into a Sql table:
 
<btb-root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" btb-num-trans="2" btb-date="2006-11-09" btb-time="22:40:03" btb-sender="BTB" btb-receipient="BPO-USR">

- <btb-request req-method="Asynchronous">

- <req-header>


  <req-btb-id>68790</req-btb-id>

  <req-client-id>1133</req-client-id>

  <req-product>BPO-Exterior</req-product>

  <req-loan-number>00000</req-loan-number>
  </req-header>

- <req-property-address>


  <addr1>1115 TEST DR</addr1>

  <city>TEST</city>

  <state>TEstate>

  <zip>30044</zip>
  </req-property-address>

- <req-borrowers borr-type="Borrower">


  <first-name>Test</first-name>

  <last-name>TE</last-name>
  </req-borrowers>
  </btb-request>
 
My goal is to take the btb-date and store it in the same table I loaded the seperate nodes to. Currently I am loading the req-header, req-property-address, and req-borrowers.
 
This date will be static in that it will remain the same for every record. My goal is to read it in and store it along with each record. Hope someone can give me some help.
 
Thanks.

View Replies View Related

How To Change Default Database 'root Directory' ?

Hi folks,

Can you please tell me how should I change the default root directory (used for db storage) in SQL 2005 Std. Edition. ?

Currently its configured to:
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
and I want to change the drive letter here.

Thanks in advance.
Cyk

View Replies View Related

Can't Create File At The Root Level Of Mount Point

Dear all,

I was trying to create filegroup and files on Mount Point, e.g. C:MOUNTFG1D1
If I try this:
.......
FILENAME = 'C:MOUNTFG1D1FG1F1.ndf',

It doesn't work, no matter what ACL I set on C:, C:MOUNT, or C:MOUNTFG1D1

But if I do this:

......
FILENAME = 'C:MOUNTFG1D1DATAFG1F1.ndf',

It will work, no need to set any ACL at all.

Any ideas?

dong

View Replies View Related

Navigation From The Root Folder To Specific Folders For Users

Hi there, I have the following setup in the report manager:
 
Root
|_ Sales
|_ Sales Export
|_ Marketing
|_ Production
|_ etc...
 
Now, I know that I can access each folder (f.e. Sales) by directly putting it into the URL. Is there a possibility to enable every user to the root folder and then denying access on specific folders? F.e.: Our Head of Sales should be able to navigate to the root folder and then dcecide whether he wants to enter the Sales or the Sales Export folder.
 
Is this possible? I tried a few things, but nothing works.
 
Thanks in advance!

View Replies View Related

Root Element MIssing &&amp; For Security Reasons DTD Is Prohibited In This XML Document

This error comes up in the print preview and nothing prints.
 
We are using VS-2005, SQL 2005 and SSRS 2005.
 
We had the same problem yesterday , so we have reinstalled IIS, .Net Framework 2.0 and Reporting Services, then reconfigured RS security, uploaded all the reports and enabled remote errors. After doing everything this error was gone and we were operating pretty fine.
 
Suddenly just we started getting the same annoying message."ROOT ELEMENT MISSING" from Report Server and from Report Manager we have "
For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method. "



 
Please Help. I need to fix this ASAP.
 

View Replies View Related







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