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