I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.
View 4 RepliesHi all,
I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file.
How can i do that.
I am using asp.net vb.net.
Hi, I use the FOR XML AUTO to retrive native XML from a database with:
SELECT [xml] FROM myxml WHERE id = 81 FOR XML AUTO, elements, root('ROOT')"
However it returns the database name and table name as parent elements. How can I return just my raw XML data without additional elements:
XML is Stored:
<ROOT>
<CHAPTER>
<TITLE>This is a test</TITLE> </CHAPTER>
</ROOT>
Returns:
<databasename>
<tablename>
<ROOT>
<CHAPTER>
<TITLE>This is a test</TITLE> </CHAPTER>
</ROOT>
</tablename>
</databasename>
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
Due to localization I have the need to make child tables, where there is a composite Primary Key, between the Id column and the LanguageSign column. On the parent table the Id column is Identity column with auto increment.
The problem is that during the select into query to copy columns from parent to child, this auto increment behaviour of the parent-Id is copied to the child-Id. However I do not want that, because the same Id will be used by different LanguageSign entries
Is there a way to use 'select into' without copying the auto increment, or is my only option to make a whole new column without auto increment on the child and copy the records?
btw I have used this statement
SET
IDENTITY_INSERT MyTable
ON , so that inserting into the Id column is possible. I can see however that this does not take away the auto increment...
Is there any suggestions for using such parameters as table name or column name in T-SQL queries ?
Please except EXEC ('string').
My server is 7.0
What is the better table design for a data collection application.1. Vertical model (pk, attributeName, AttributeValue)2. Custom columns (pk, custom1, custom2, custom3...custom50)Since the data elements collected may change year over year, whichmodel better takes of this column dynamicness
View 7 Replies View RelatedHi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20))
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ProjectNumber int
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SELECT MAX(@ProjectNumber)
FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','');
END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.
Hi,
I would like to know how to add SELECT row to a table variable. It's not for my SELECT syntax(code following is just an ugly example) that I want help it's for the use of table variable.
Your help will greatly appreciate!!!
ex :
DECLARE @MyTestVar table (
idTest int NOT NULL,
anotherColumn int NOT NULL)
SET @MyTestVar = (SELECT idTest, anotherColumn FROM tTest)-- This cause an error :-- Must declare the variable '@MyTestVar'. ???? What?
I have a table that has a primary key that is auto incremented by 1. This table's data is cleared out periodically and as data gets added the auto id primary key continues to increase in numeric value. Once the data is cleared from the table the auto id names could be used again(the eventId is not stored) Currently the eventID is at 26,581,399. I know the maximum int value is 2,147,483,647.
How should I handle this? or rebuild the table every time the data is cleared(problematically)?
I have created a number of reports where I need to display database elements in the Header record. I have been using the reportitems feature to get them to display there. When I preview the report the header looks fine; however, when the report is exported to pdf or printed out the header information completly disappears.
Any suggestions would be great.
Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
View 2 Replies View RelatedI am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable.
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in
-- descending order
SELECT * FROM @tblCustomRange
WHERE (RangeMonth < @Month) OR
(RangeMonth = @Month AND RangeDay <= @Day)
ORDER BY RangeMonth, RangeDay DESC
I am getting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
2 1 27
1 1 2
3 6 10
I am expecting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
3 6 10
2 1 27
1 1 2
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end
Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'
Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'
Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'
select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type
from @NumericBase
SET NOCOUNT OFF
Hi All,
I have a requirement here to import data from XML file to SQL Database. The XML schema contains of various elements and one of the element is recursive ie. Parameter node contains parameter node within it and it can have n number of iterations. I have given the sample schema below:
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
</xs: sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Parameter">
...............
</xs: sequence>
</xs:complexType>
</xs:element>
</xs: sequence>
</xs:complexType>
</xs:element>
But all the nodes contain the data which has to be imported to a single table dbo.Parameters. I cannot use Union ALL since i dont know how many iterations I will have in the file. Is there any way to do this operation in Data Flow Task using XML Source? Can anyone help me on this?
Thanks,
Dhileep
I am trying to use FOR XML under SQL Server 2014 to write out a large XML data set. I want it to look like
<CVS_Member_Add_Change>
<RecordType>3</RecordType>
<Carrier>1266</Carrier>
<MultiBirthCode>0000000</MultiBirthCode>
<MemberType></MemberType>
[Code] ....
That's how it looks when you click on the results of a small subset of the query. Just what I want. Unfortunately when you try to right click and save it you get
<dataroot><CVS_Member_Add_Change><RecordType>3</RecordType><Carrier>1266</Carrier<MultiBirthCode>0000000</MultiBirthCode><MemberType></MemberType<LanguageCode>1</LanguageCode><DURFlag></DURFlag><DURKey></DURKey><SocialSecurityNumber>000000000</SocialSecurityNumber</CVS_Member_Add_Change>
Everything being on one line blows up the translator application that reads the data.
The FOR XML statement copied out of the query is below.
FOR XML RAW ('CVS_Member_Add_Change'), ROOT('dataroot'), ELEMENTS
GO
Is there a way in the T-SQL to force it to break lines neatly?
Is there a way to force it to a specific file name or directory?
We are using a table that may give 1 to and unknown number of data elements (ie. years) . How can we break this to show only three years in each row. Since we don't know the number years we really won't know the number of rows needed. Years are stored in their own table by line.
car make year1 year2 year3
A volare 1995 1996 1997
a volare 1997 1998 1999
b toyat 1965 1966 1968
We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.
Here is my objective:
As part of auditing of our application, I want to write the values of all column in one row out to an audit table both before and after an update to see what the changes were.
I know I can SELECT * FROM tableName FOR XML AUTO to generate a single XML 'column', but I cannot figure out how to capture that output and store it.
According to MS, you cannot use FOR XML in a subselect, or fill a TEXT variable with the results of such (which, IMHO really diminishes the usefulness).
I want to stream out the results of one entire row and store that in ONE column of an audit table. XML seems like the easiest way if not for this limitation.
A better way?
Thanks.
I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?Thanks in advance,Sunny Guam
View 3 Replies View RelatedI am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
Im really new to this and Im really bad.
but im trying to get the userID From a table,
but the table is named "Table"+the productID
ie Table23
or Table54
has 2 columns, UnitId and UserID
heres my code
CREATE Procedure UserDetail
(
@UnitId int,
@ProductID int,
@UserID int OUTPUT
)
AS
declare @TSQL varchar(1000)
SET @TSQL= 'SELECT @UnitId , @ProductID , @UserID ' +
'FROM Table'+CAST(@ProductID AS char(20))+
' WHERE UnitId=@UnitId '
execute(@TSQL)
GO
and i get this error
Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@UnitId'
anyhelp would be aprciated,. thanks so much
Can we retrieve data from table type variable as an array in oracle key values..
E.g.
ALTER PROCEDURE prn1( @p_prn as KeyValuePair readonly)
-- proc which having parameter table type as parameter
AS
declare @v_formatted_str varchar(50)
BEGIN
set @v_formatted_str = v_formatted_str + @p_prn(1)(1)
END
How to insert data into a table from a string variable? Like below:
DECLARE @Data AS NVARCHAR(MAX)
SET @Data = 'bla|bla|bla
lab|lab|lab
abl|abl|abl'
BULK INSERT tablename
FROM @Data
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = ''
);
I have an application where the columns in a datatable stored in a variable (as a dataset with one table in it) are dynamic. This means that the number of columns and their data types will vary. Also, I would like to access the column names. For these reasons it is not very practical to map the columns to variables.
Is there a way to use a Script Task within a For Each Loop (with ADO enumeration) to read the data row by row (presumably as a datarow) getting the column names and coloumn values by numeric (column) index?
Thanks,
*** Campbell
I have the following data
MASTER
id
name
DETAIL
id
master_id
name
I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.
How can I do that???
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
is there a way to see the data of a table variable in the SSMS debugger? For example, if I set a breakpoint in SSMS and look at a populated table variable named @MyTable in the Locals tab at the bottom of the IDE, a value of "(table)" is displayed. There does not appear to be a way to expand or drill into this variable in the debugger to see the data. Do you know if there's a way to do this through the debugger or do you use an alternate approach when using the SSMS debugger?
View 1 Replies View RelatedI have created procedure that creates a temptable with the users from a certain business unit.
I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.
CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)
[Code] ....
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?
View 3 Replies View Related
I am using a foreach loop, with the data from an ado recordset, which contains the table name that I wish to write data to an OLEDB data dest. The table names are retrieved from an execute sql task in the an object var. Within the foreach loop, for each table name, I then use a datareader to an ado.net source to pull data from that table, via an expression construct into a variable - i.e. "select * from " + @[User::table_name]. This works fine for the first table, in which mappings are setup using the SSIS design environment. The data is retrieved. I then use a variable and set the data access mode for the oledb destination to "Table name or view name variable". This also saves data fine for the first table in the loop in the oledb dest. When the next table name is retrieved from the ado provider in the foreach loop, the datareader fails, as it still thinks the metadata mappings are from the first table, which was used for the mapping in the design environment. I.E. FIN_CLASS is a column from the first table in the loop.
Error: 0xC0202005 at Data Flow Task, DataReader Source [7181]: Column "FIN_CLASS" cannot be found at the datasource.
I have set the following properties, that I thought (in my feeble mind), are supposed to avoid that behavior. For the datareader, I set ValidateExternalMetadata to false, and for the data flow task (container for the datareader), I set DelayValidation to true. These settings, according to the doc, are supposed to evaluate metadata for the datareader source at runtime (not design time), so that the column metadata is dynamic, and so that the subsequent oledb destination can use the "data access mode" for the oledb destination of "Table name or view name variable".
If I cannot get this to work, I have 2 options: Use OPENQUERY via dynamic t-sql statements, OR create 30 separate flows in SSIS - one for each table - not looking forward to that one.
I am using C# in Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();
i'm using ms sql server 2008 r2 advanced express.
i have a select statement that generates my google shopping feed.
is there a way to get this statement run automatically and export the results to a tab delimited csv file?
i'm currently using an asp script to create an xml file for the feed, but it's putting too much strain on the server.