SQL Server 2012 :: Nested FOR XML Path

Oct 16, 2014

I'm trying to reverse engineer an XML output based on a client's need of a very specific format. Besides the issue of getting the XML declaration written in and removing NULLs, I'm having an issue with a three times nested PATH query.

So far the document almost has the correct format, except for the first nested root being returned. Is there any way to prevent this?

*some data redacted

Query:

DECLARE @a XML
SET @a =
(SELECT FileCreationDate,
(
SELECT PCE_TPD.PJN,
(
SELECT top (2) TaskCode, TaskName, RSI, TAFFD, AFD, Notes, PID, CUSID, NeedToBeNA

[Code] ....

Current Output (first few lines):

<TDBUIData xmlns="http://www.xxxxx">
<TDBUIData12 xmlns=""> --NEED TO ELIMINATE THIS
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>

[Code] ...

Desired Output (first few lines):

<TDBUIData xmlns="http://www.xxxxx">
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>
<TaskData>

[Code] ...

View 4 Replies


ADVERTISEMENT

SQL Server 2012 :: How To Implement Nested Tables

Dec 15, 2014

I need to perform some operations that seem to require multiple nested tables.

Is the following code the way I should be attempting this or is there a better way?

Here is the sample code:

SELECT tt.tdl_ID, tt.tx_ID, adj.Prof_Chgs, rc.Rvu_Comp
FROM Table1 as tt
LEFT JOIN (
SELECT tt1.Tdl_Id,
CASE
WHEN tt1.tdl_ID = '1000'

[code]....

View 8 Replies View Related

SQL Server 2012 :: Encoding With Single Apostrophes In FOR XML PATH

Oct 16, 2014

Here is my problem:-

declare @test as varchar(32)
declare @test2 as varchar(32)

set @test='today''s problem'
set @test2='my <string> '

select @test as '@attribute' for xml path ('myrow')
select @test2 as '@attribute' for xml path ('myrow')

I want for xml path to correctly encode the single apostrophe as &apos but the single apostrophe doesn't get encoded. In the second example the greater and less than does get encoded.

View 4 Replies View Related

SQL Server 2012 :: Compare A Table With Physical Path

Mar 31, 2015

I have a table with two columns

id | filepath
--------------------------------------------------
1| D:Doc filesThe BestHHT.JPG
2| D:Doc filesThe Bestsealed_pack.txt
3| D:Doc filesThe Bestlsbom.JPG
4| D:Doc filesThe Bestmoc.png
5| D:Doc filesThe Beststock.txt
6| D:Doc filesThe Bestdepot.JPG

And in a physical system there are more files than the table.

D:Doc filesThe BestHHT.JPG
D:Doc filesThe Bestsealed_pack.txt
D:Doc filesThe BestJKSlsbom.JPG
D:Doc filesThe Bestmoc.png
D:Doc filesThe Beststock.txt
D:Doc filesThe BestGDNdepot.JPG

D:Doc filesThe BestCASA.JPG
D:Doc filesThe BestSO.txt
D:Doc filesThe BestBA.JPG

I want to compare the filepath column in table with physical drive files and get the details of files which in table and not in physical and viceversa...

View 3 Replies View Related

SQL Server 2012 :: Structure For Multiple Nested Queries

Feb 27, 2014

I am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.

Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.

I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.

Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.

IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
SELECT
cohort.PatientSID
,SubQuery1.LabChemResultNumericValueAS 'A1c%'
,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date'
,SubQuery2.LabChemResultNumericValueAS 'LDL'

[Code] .....

View 1 Replies View Related

SQL Server 2012 :: How To Copy Nested Sub-tree From One Node To Another

May 12, 2015

I have a tree and I need to copy a nested sub-tree (an element with its children, which in turn may have their owns) from one place to another.

The system should allow to handle up to 8 levels. I do know how to move, but cannot figure out how to copy.

Below is a working example With Create Table, Select and Cut / Paste (implemented via Update).

I would like to know how to copy a nested tree with reference id 4451 from Parent_Id 1 to Parent_Id = 2

--***** Table Definition With Insert Into to provide some basic data ****

IF (OBJECT_ID ('myRefTable', 'U') IS NOT NULL)
DROP TABLE myRefTable;
GO
CREATE TABLE myRefTable
(
Reference_Id INT DEFAULT 0 NOT NULL CONSTRAINT myRefTable_PK PRIMARY KEY,

[Code] ....

How to Copy nested sub-tree 4451 with all its children to Parent_Id 2, without deleting from Parent_Id = 1 ?

View 7 Replies View Related

SQL Server 2012 :: Nested Query Retrieving Min And Max Values?

Jul 16, 2015

I have a nested query that retrieves a min value. I would like to know how do i retrieve the corresponding time with that.

my data looks like this

valuevaluetime
1212/31/14 14:51
12.42/19/15 23:30
[highlight=#ffff11]10.92/21/15 6:40[/highlight]
13.21/25/15 20:47

My min value is 10.9 and i need the date 02/21/15

my nested query is as follows

( select min(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time)

the above returns me the 10.9

i modified the query

select min(cast(f.valuestr as float)),
f.valuetime
from bvfindings f
where f.ObjectName = 'Hb'

but i get all values then.

View 6 Replies View Related

SQL Server 2012 :: Retrieve Column From Nested Query

Aug 6, 2015

I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?

SELECT
s.facilityid,
s.sessionid,
s.MRN,

[code]....

View 2 Replies View Related

SQL Server 2012 :: Bulk Insert Using UNC Path Of File Table Directory

Jul 22, 2013

Overall goal: Write a Bulk Insert statement using the UNC path of a filetable directory.

Issue: When using the UNC path of the filetable directory in a Bulk Insert Statement, receiving "Operating system error code 50(The request is not supported.)" Looking for confirmation as to whether this is truly not supported.

Environment: SQL Server 2012 Standard. Windows Server 2008 R2 Standard

View 9 Replies View Related

SQL Server 2012 :: Using Recursive Query To Find Path Between Assembly And Parts?

Jul 2, 2014

I'm trying to use a recursive query to find path between assembly and parts.

The BOM is similar to(I've limited the number of rows and lines):

BOM NumberMat Number
20000222001770
20000222003496
20000222001527
20000222003495
20002462002005
20005062000246

[code]....

How should I modify it so that is returns the 4 path?

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

SQL Server Not Starting - Tempdb Path Updated To Wrong Path

Oct 4, 2007

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

SQL 2012 :: Nested Procedures And Try Catch

Mar 11, 2014

I have 2 Procedures. P1 and P2. Even if there is error in P1 , I want changes in the P2 to be committed. How do I do it?

Following is the code sample I have used for testing. With this code , update in both the procedures are rolled back.

create procedure P2
as
begin try

BEGIN tran
update table1
set Description = '*P2*' + Description
where Code = 'b'

[Code] ....

View 2 Replies View Related

SQL 2012 :: Deadlock Information And File Path

Sep 9, 2014

I have enabled 1222 and 1204 trace and restarted sql server. then i have replicated deadlock in my local.

This is the error came in sql

Msg 1205, Level 13, State 45, Line 1

Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Then i opened ERRORLOG file , there is no information about deadlocks .

Where can found deadlock information and file path. is there any extra options required to enable trace?

DBCC TRACESTATUS(1222,-1)

TraceFlagStatusGlobalSession
1222110

How to find location trace log?

View 9 Replies View Related

SQL 2012 :: Modifying Variable Value (Source Path)

Nov 12, 2014

We have a job running the SSIS Package. I am working with that SSIS Package. The SSIS package has a script task. In the script task they used the variable of source path. I want to change the value of that variable (Need to modify the source path).

I modified the variable value in that package and in the config file but getting error message

Exception has been thrown by the target of an invocation.

View 2 Replies View Related

SQL 2012 :: How To Change File Path From C Drive To E

Oct 22, 2015

How do I change the file path from C: drive to E:

See attached

View 2 Replies View Related

SQL 2012 :: Upgrade Advisor - The Network Path Was Not Found

Jul 2, 2013

We installed the SQL Server 2012 GUI tools, including "Upgrade Advisor," on a new virtual machine. We can connect to the old SQL Server 2008 R2 clustered databases just fine over port 1433 using SQL Server Management Studio 2012, but Upgrade Advisor is a mixed bag. Here are the steps we are taking:

- Click on "Start," "All Programs," "Microsoft SQL Server 2012," "SQL Server 2012 Upgrade Advisor"

- Fill in the server name with a valid SQL Server Network Name

- Select "SQL Server" as the only component to be analyzed

- Fill in the instance name with a valid named instance

- Use Windows Authentication (the default)

- The "SQL Server Parameters" window displays valid database names, meaning that we have a successful connection to the desired SQL Server instance at this point

- Leave all of the databased selected (the default)

- Click the "Run" button

- The "Upgrade Advisor Progress" window spins for a while and eventually generates the following error:

TITLE: mscorlib
------------------------------
The network path was not found.
------------------------------
BUTTONS:
OK
------------------------------

Why would Upgrade Advisor be able to display the database names after making a successful connection only to fail later saying that it cannot find the network path?

View 4 Replies View Related

SQL 2012 :: Long Path Names To Files On Installation

Dec 13, 2013

I'm getting a rule check fail "Long path names to files on SQL Server Installation media failed" when installing SQL 2012 Standard edition from a network share.

View 8 Replies View Related

SQL 2012 :: Converting Directory File Path Into HierarchyID

Jul 22, 2014

I am trying to create hierarchyID for a directory file path.

View 1 Replies View Related

SQL 2012 :: Rollback Transaction In Nested Stored Procedure?

Nov 24, 2014

create proc proc1 (@param1 int)

as

begin try
declare @param2 int
begin transaction
exec proc2 @param2
commit transaction
end try
begin catch
if @@trancount > 0
rollback transaction
end catch

i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.

View 3 Replies View Related

SQL 2012 :: SSIS FTP - Invalid Local Path / Trying To Send Files

Oct 27, 2015

I have created a FTP Task which is returning the value, "Error: 0x0 at FTP Task, SFTP Task: Invalid local path."

In my DB Table, the table contains a field for the location of files to be Ftp'd which is: C:/temp/ftp/OB/*

I believe my settings are correct and am curious to know if the above string should work or if I need a different syntax for the local variable.

View 0 Replies View Related

SQL 2012 :: Nested Tables And User Defined Data Types?

Jun 5, 2015

I have a requirement of creating nested tables in SQL server. how to create them. Just to give a background I am trying to move the RDBMS from oracle to SQL server.

Structure of tables is as follows. I have table 'Employees' with address as one of the column. I have one more table with columns Street, Town, Dist, State. When I query the table 'Employees' I should see the attribute name and values of all the columns in address table in address column.

Employees: with columns: ID, FirstName, LastName, dept, gender, dob, address

Address (Nested table): with columns : Street, Town, Dist, State

This was done in oracle using Nested tables and user defined data types. what is alternative for this in SQL server. How can I achive this requirement in SQL server.

View 2 Replies View Related

SQL 2012 :: Log Shipping - Secondary Database Path Showing Wrong Location?

Mar 17, 2014

I've configured log shipping to use for DR purposes. I'm concerned that the physical location of the secondary is mis-reported by SQL Server Management Studio.

Viewing the secondary location (with Studio DB_name Properties Files) shows the path of the primary DB (I expected it to show the path of the secondary).

This SQL command shows the correct/actual paths of both primary and secondary DB's when run on their host servers.

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files

Is this just cosmetic?

Here is an Example of how the Studio shows the incorrect path for the secondary.

Example:

(Primary) servername=prodSrv, DBname=aquaDB, Actual_Path=G:aquaDB, SQL-studio-Properties-Path=G:AquaDB, sys.master_files Path=G:AquaDB,

Log shipped to

(Secondary / Read Only) servername=DRSrv, DBname=aquaDB, Actual Path=F:aquaDR, SQL-studio-Properties-Path=G:AquaDB(WRONG), sys.master_files Path=F:aquaDR

View 2 Replies View Related

SQL 2012 :: How To Fetch Single File From Local Path Using SSIS Package

Nov 14, 2014

I have 15 Dealers files .With the Files name as follows

1. ''ACTEST00001_20141112_0408_INV.TXT''

2. ''ACTEST00002_20141112_0408_INV.TXT''

I will get these files through FTP on Daily basis with changes on date alone.

I will have 4 files for each dealers.like INV,SERVICE,SALES,APPOINTMENT.

SO i need to fetch a particular dealer with particular date.

I need to create this scenario in SSIS package. How to create this and what are all the Tasks i need to use to implement this process.

View 1 Replies View Related

SQL 2012 :: SSRS Virtual Directory - HTTP GET Method Appending Default Path

Sep 27, 2015

There was a 2008 SSRS service running on server "abc".

I have a .Net UI that uses the wsdl at [URL] ....

They deployed SSRS 2012 service on a new server "xyz".

DNS points abc to xyz. Nslookup looks right shows both xyz and abc alias to the same IP.

when I try to go to [URL] .... it redirects to xyz as expected for the host name but the HTTP GET /ReportServer is appended with GET /Reports/ReportServer which doesn't exist and I get an 404 error.

I have no admin rights to see anything and I have no idea if it was an inplace upgrade or a new server. I assume it's new since the actual server name changed in nslookup. Why would it append the default virtual directory when it makes the hop to server "xyz"?

View 0 Replies View Related

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

Apr 16, 2008

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

Path When Restoring Db From SQL Server

Jan 2, 2008

When  I restore db from backup on another server, it always remebers the source db path. This is very annoying as drive mappings are different. I have to go and manually change those paths each time to point to the existing mdf... Is there any way so I do not have to do this??? 

View 3 Replies View Related

Physical Path Of Sql Server Log

May 30, 2008

hi
where is save the folder of sql server logs ,error logs in sql server 2005

View 2 Replies View Related

Sql Server Nested Set, How?

Nov 8, 2006

Hi, I'm not sure if this is  a good place to ask sql questions, so please bear with me here...I have a table like soid, parentidWhat I'm trying to do is to write a self join where given a random ID, it'll give me the  whole tree of its decendents. (I don't need its parent)so say I have data liek soid     parentid1      null2      13      2when I specificy 1, it'll give me 2 and 3, even though 3 is indirectly related to 1.  when I say 2, it'll just give me 3. Thanks a lot. GREATLY appreicate it.  

View 1 Replies View Related

Linked Server To Excel With UNC Path

Apr 5, 2006

We have been having problems getting a linked server to an excel file
working with an UNC path. If the UNC path is to the SQL server itself
it will work but not if the UNC path is to another server.

The SQL server 2000 SP 4 processes are running under a domain id and we
can logon interactively with that same login id and we can access the
excel file via the same UNC path.


We have tried it with setting up a linked server and also linking to it
'on the fly':


SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\AnotherServerUNCpathexcel.xls";User
ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$
go


The message we are getting is the following:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The
provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not
give any information about the error.].


It appears from reading Microsoft's documentation and other topics that
this should be possible. Any ideas on what we are missing?

View 6 Replies View Related

SQL Server 2008 :: Extract Name From UNC Path

Feb 4, 2015

Running into some difficulty trying to extract a name from a UNC Path. The structure of the UNC Path is this:

server1-1shareusername hispartgoesonandon

I'm needing to extract "username" into a separate column.

View 2 Replies View Related

Get SQL Server Express Installation Path

Mar 26, 2008

Hi, i would like to know if there's a way to know the path where my sql server is located, i need to know this because i've an application (VB .NET) that needs to know this path, any help?

View 1 Replies View Related

Recursive Complete Path On SQL SERVER 2005 ???

Apr 18, 2008

Dear,

I'm having this table:

PathIDParentPathIDPath
1 NULL D:
2 1 Sections
3 2 Bin
4 3 Data
5 4 FinancialReport.doc
6 4 DebtReport.doc
7 3 db.dll

I would like to create a store procedures that will return me a full path by passing a PathID

I started with this code:

DECLARE @path_id int
SET @path_id = 5;
WITH fullPath (PathID, ParentPathID, Path)
AS
(
SELECT PathID, ParentPathID, Path
FROM tblPath WHERE PathID = @path_id
UNION ALL
SELECT tblPath.PathID, tblPath.ParentPathID, tblPath.Path AS Path
FROM tblPath
JOIN fullPath ON tblPath.PathID = fullPath.ParentPathID
)
SELECT * FROM fullPath

This code will return this:
1 NULL D:
2 1 Sections
3 2 Bin
4 3 Data
5 4 FinancialReport.doc

What I would like to get is something like this:
D:/Sections/Bin/Data/FinancialReport.doc

Any help would be really appreciated. Lost too much time on it already.
Thanks,
pharvey

View 9 Replies View Related







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