FOR XML Sub Root

Apr 6, 2008

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 4 Replies


ADVERTISEMENT

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

Dec 4, 2007



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 1 Replies View Related

Root Node From A FOR XML

Aug 26, 2005

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 5 Replies View Related

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

May 28, 2003

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 11 Replies View Related

XML Source - What About The Root Element?

Apr 7, 2008

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 4 Replies View Related

Retrieve Value Of The Root Attribute In XML

Apr 2, 2008

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 6 Replies View Related

How To Change The Root Directory?

May 23, 2007

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 7 Replies View Related

Master Database On Root Drive??

Apr 3, 2002

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 3 Replies View Related

TempDB Files On Root Of Drive

Sep 15, 2015

Documentation that supports the placement of Tempdb files on the root of a drive, i.e T: instead of T: empdb. I am positive this is not a best practice, but when challenged could not find any documentation that would support that view.

View 7 Replies View Related

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

Jan 17, 2006

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 3 Replies View Related

Root Directory Of SSIS Package

Jan 12, 2008

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 3 Replies View Related

Root Element Is Missing Problem

Apr 22, 2008

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 4 Replies View Related

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

Dec 17, 2006

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 10 Replies View Related

How To Move Server Root Directory To Another Partition?

Apr 18, 2000

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 1 Replies View Related

Putting Attribute On Root Node Using FOR XML PATH

May 17, 2006

I would like to know how to put an attribute on the root node of thexml returned from a FOR XML PATH query. One thing I tried is this:selectm.msgid '@msgID',st.namelong 'set/@namelong',st.nameshort 'set/@nameshort',frommsgset minner join settable st on (st.setid = m.setid)where m.msgID = 195for 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 tagomission (empty row tag name) cannot be used with attribute-centric FORXML serialization." I'm sure there is a trick to this-- anysuggestions?Many thanks.the chippster

View 5 Replies View Related

Remove ROOT Tags From SQL Task XML Results

Dec 19, 2007

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 9 Replies View Related

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

Jul 27, 2007

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 4 Replies View Related

How To Change Default Database 'root Directory' ?

Feb 27, 2006

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 4 Replies View Related

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

Aug 28, 2007

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 3 Replies View Related

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

Sep 14, 2006

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 2 Replies View Related

Navigation From The Root Folder To Specific Folders For Users

Apr 1, 2008

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 5 Replies View Related

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

Aug 10, 2007

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 8 Replies View Related

SQL Server 2012 :: View To Concatenate File / Path Names Back To Root Directory?

Sep 25, 2014

We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root

Below is a sample:

Id Name IdParent
1 C: NULL
2 D: NULL
3 ProgramFiles 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4

iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.

I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.

Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)

The end result of my view on the above data should return:

Id FilePath
5 C:ProgramFilesExcel.EXE
6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)

View 5 Replies View Related

Problems With Change Sql Permissions After Migrating Domain User/group Accounts Into Root Domain

Apr 5, 2007

I have a root domain and child domain.



After using ADMT to migrate the domain user or group into the root domain, when I use enterprise manager to try and change the permissions allocated to that domain user/group, i get the 'Error 15401 NT user or Group not found'.



This is a correct error as the user is now in the root domain, however sql (in sysxlogins) still thinks its in the child domain.



Is there a simpler way, other than collecting the users permissions, deleting the user from SQL then adding back in with the correct domainusername format, then adding the permissions back?



I tried renaming the 'name' in sysxlogins (not recommended) and while that worked, whenever I tried to add the migrated user to another database, the login name was missing and would not resolve.



I believe it is something to do with the SID not matching.



Any ideas on how to fix this ?

View 1 Replies View Related

[XML Task] Error: An Error Occurred With The Following Error Message: There Are Multiple Root Elements.

Aug 18, 2006

I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:

[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".

The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:

<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>

There is only 1 ESDU root element and only 1 package element.

Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:

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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>

<xsl:template name="identity" match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>

<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>

</xsl:stylesheet>


Its 5th line is the first xsl:template element.

What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.

Thanks!

View 5 Replies View Related







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