Bulk Copying Xml Child Node Data
Aug 23, 2006
I am trying to bulk copy some XML data into a SQL and am generally doing quite well. The XML data I have been given has a child node called "name" which is the same as in the parent node as shown in the highlight of the XML source below. Now I can retrive the data in the parent node using
bulk.ColumnMappings.Add("name", "Name")
but I cannot get any of the data from the child node "Catagory" or " Catagories". Have you any suggestions on how I can get this data.
<?xml version="1.0" ?>
<
products
>
<
product
>
<
ProductId
>
12345
</
ProductId
>
<
name
>
Productname
</
name
>
<
description
>
"This is some description
text"
</
description
>
<
Categories
>
<
Category
>
<
name
>
Category type
</
name
>
<
merchantName
>
Category subtype
</
merchantName
>
</
Category
>
</
Categories
>
<
fields
/>
</
product
>
Etc…….
</
products
>
Many thanks in advance
Simon
View 1 Replies
ADVERTISEMENT
Oct 23, 2007
Hi guys iam working on Sales Force Automation Application in which i have a senario where i create team (base table) and team users (child table) one team can have many users and so user can be in one or more team.
My question is what will be the best practise (performance wise) to add let suppose 100 users in a team in one go for example user can create of any number users present in the user list or How to insert bulk of records in child table .>>
iam using sql server 2000 +Asp.net 2005 (c#)
View 1 Replies
View Related
Jul 13, 1999
I'm having difficulty bulk-copying a data file, using a format file, into a SQLServer 6.5 table with an identity column.
The data file is tab-delimited, and does not include the identity column (I want it to be automatically generated).
When I execute the following:
bcp DB1.dbo.WORK_TABLE in WorkTable.txt -Usa -P -fWorkTable.fmt -SSERVERX'
I receive this message:
Starting copy...
BCP copy in failed
I've had no problems loading a similar table without the identity column.
Can anybody help? Here is the table definition & bcp format:
CREATE TABLE WORK_TABLE
(
PersonID int NOT NULL IDENTITY (1,1)
,FirstName char(25) NULL
,MiddleName char(25) NULL
,LastName char(25) NULL
,Company char(50) NULL
,City char(25) NULL
,State char(2) NULL
,Phone char(25) NULL
)
-----
6.0
8
1 SQLCHAR 0 0 "" 1 PersonID
2 SQLCHAR 0 25 "" 2 FirstName
3 SQLCHAR 0 25 "" 3 MiddleName
4 SQLCHAR 0 25 "" 4 LastName
5 SQLCHAR 0 50 "" 5 Company
6 SQLCHAR 0 25 "" 6 City
7 SQLCHAR 0 2 "" 7 State
8 SQLCHAR 0 25 "
" 8 Phone
-----
View 2 Replies
View Related
Apr 16, 2007
Hi,
Im trying to make a replica of sqlserver 2005 replication.
In that main bottle neck is ..bulkcopying of data from publisher table and to subscriber table.
how to avoid this bulk copying while creating subscriber for a given Publisher.
regards
Baji Prasad.B
View 4 Replies
View Related
Jan 17, 2008
Im having some issues with bulk insert.
This is the table:
CREATE TABLE [dbo].[tmp_GA_status](
[GA_recno] [int] NOT NULL,
[GA_desc] [varchar](40) NULL
)
This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"
and this is the sql:
bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'
with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')
so yeah, pretty simple. But whatever I do I get this;
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).
So what am I doing wrong ?
View 13 Replies
View Related
May 19, 2015
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
[Code] .....
View 6 Replies
View Related
Apr 20, 2015
I have not used log shipping before and find myself in a position where I need to reboot the secondary node and then the primary node and I don't actually need to failover.
Is there anything I need to be aware of. When rebooting the secondary node I assume the transactions will be held in the primary nodes log till the secondary comes back and just carry on once back up?
When rebooting the primary node nothing needs to be done and the log shipping will just start again once it has come back?
View 3 Replies
View Related
Jun 12, 2007
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 1 Replies
View Related
Oct 23, 2014
I'm contemplating running two availability groups on a two node WSFC. The WSFC is setup with a file share witness (i.e. no shared storage). Can I safely run 1 AG on one primary node, and the other AG on the other node (as primary). Each AG would have replicas on the passive node. This would effectively allow both servers to be in use at the same time. In a failover event, I understand that both workloads would transfer to a single server - so the box needs to be sized appropriately.
View 1 Replies
View Related
Jun 11, 2015
We are in the process of building a 3 node SQL Server Cluster (Server 2012/ SQL Server 2012), and we have configured the quorum so that all 3 nodes have a vote (no file share witness as we already have an odd number of nodes).
As I understand it, this should allow the cluster to run as long as 2 of the nodes remain online.
However, the validation report states that 2 node failures would be acceptable and, when we tested this by powering off two of the nodes, the cluster did indeed continue to run on a single node.
View 4 Replies
View Related
Jun 29, 2015
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
View 5 Replies
View Related
Aug 21, 2007
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 2 Replies
View Related
Aug 3, 2013
CREATE TABLE #Source
(
Id int identity(1,1)
,categoryint
,Leaf_Node_code varchar(10) --
,Level1_Name varchar(20)
,Level2_Name varchar(20)
[Code] ....
Here category 1 has 3 levels ,
category 2 has 4 levels ,
category 3 has 5 levels ,
Below is the target table, here Leaf_Node_code should populate to only for leaf nodes for each category .. Need to populate Node_id with hierarchical data
I am unable frame a sql query to handle different levels , in future #Source may have more levels .
How to handle multiple hierarchy levels .. here only leaf node should have Leaf_Node_code
CREATE TABLE TARGET_TABLE
(
ID INT IDENTITY(1,1) primary key
,Node_id HIERARCHYID
,category int
,Parent_id int references TARGET_TABLE(id)
,Leaf_Node_code varchar(10)
,Namevarchar(20)
)
Here is the expected output:
IDcategoryParent_idLeaf_Node_codeName Node_id
11NULLNULLWorld
211NULLAsia
312101India
42NULLNULLa
524NULLaa
625NULLaaa
726102aaaa
83NULLNULLb
938NULLbb
1039NULLbbb
11310NULLbbbb
12311103bbbb
View 1 Replies
View Related
Jan 16, 2008
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 1 Replies
View Related
Nov 14, 2007
I Have a problem when copying data from one server to another in Management studio, I need to create and exact copy of the original because of primary key relationships,
Currently when I export the data the data will run through an insert type statement, which means that all PKs are reissued, rather than being duplicated from the original, How can I be sure that the data will be copied exactly how it is on one server to the other.
View 4 Replies
View Related
Jan 2, 2008
i've created a package that will copy data from an oracle table to a sqlserv table (that table elemenst are identical), when i click on the connecting line between the two connections it executes without any errors, but nothing is copied, when i try to execute the package i'm getting an error... where can i go to find out what's causing the error. There is no error message or number returned from dts, all i get is a red 'x' . There are 1236 records that need to be inserted and when i get the red 'x' it tells me 1236 records have been processed. When i click on tranformations and select test, it works, but since it's a test nothing is actually copied. I've got other packages that i've created that do the same thing with other tables and they work. I tried just copying one record and that worked, so i assumed it must be data dependent, i've checked all the fields and made sure they weren't null, i've checked to make sure there aren't duplicate primary key records. Without knowing what the actual error is I'm stumped ???
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
Jul 25, 2014
Basically i have three Tables
Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia
Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789
Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789
I want the Result
Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2
View 1 Replies
View Related
Oct 22, 2007
............child |parent|.............a1 |a | .............a2 |a |.............a11 |a1 |.............b1 |b |.............b11 |b1 |.............b111 |b1 |.............
Here is my table I want to get all childs of "a" ie {a1,a11,a2}I mean recursivelyie child,chid of child ,child of child of child ........etc up to any extentet the table containHow can i for mulate the select querry?
View 3 Replies
View Related
May 9, 2008
Hi all!
I am trying to organize a hierarchical data structure into a table. I need to have the possibility to set 2 parents for some nodes. Curently I see following two options:
Example 1
id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1,2 Level 2 Child
Example 2
id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1 Level 2 Child3 2 Level 2 Child
Is any of the two examples valid database logic wise? In fact, is it possible to achieve the requirement by using only one table?
Thanks in advance,
View 4 Replies
View Related
Jun 26, 2015
I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Looking for below output ...
View 3 Replies
View Related
Mar 30, 2007
Is it possible to easily copy data from one table to another if the data types don't match. I know you can do a INSERT INTO table1(col1,col2) SELECT (col2,col7) FROM table2 if the data types match but is there a way to do this if they don't. I'm not trying to copy date times into bit fields or anything. I just have an old table that I built when I really didn't know what I was doing now I at leastthink I have a better understanding of what data types to use, so I was wanting to move the data in the orignal table to my new one. Most of the fields in the olddatabase are text datatypes and the new database is nvarchar(50) data types. Thanks for any suggestions.
View 4 Replies
View Related
Apr 28, 2006
I've got two DBs in the same SQL instance. They are named TST and PRD. I am using 2.0 so there are many ASP generated tables also. Every once in a while I want to refresh data from PRD to TST. But I don't want to copy the data from ASP tables.What is the easiest way to do so?
View 6 Replies
View Related
Oct 10, 2006
Rene writes "Good evening. This is my first time using sqlteam for answers and I'm hoping I can get some much needed direction.
Basically what I am trying to accomplish is taking records from a temporary source table to a permanent table. Here is what the tables look like (oversimplistic version)
tblTemp
ID
Value1
Imported
FailedReason
tblPerm
ID
Value1
Basically I would like to take the values from tblTemp and INSERT them into tblPerm. The catch is that values in tbltemp might violate primary key constraint because of duplicate values in the ID field. The value1 field is required in tblPerm and it might contain a null value on tbltemp causing the insert statement to fail.
What I would like the end result to be is that any records which are INSERTED from tbltemp to tblperm are flagged with a value of imported=1 on the tbltemp table. Any records which fail should then be flagged as imported=0 and failedreason=reason for failing.
I am trying the following to start with but am not sure if I am steering in the right direction or not. The process should be as automated as possible, perhaps part of a scheduled dts package or likewise since new data will be inserted in the tbltemp table on a weekly basis.
set rowcount 1
update tblsource set imported = 0 where imported is null
insert into tbldest (col1, col2)
select top 1 col1, col2 from tblsource where imported = 0 and notimportreason is null
IF @@Error <> 0
GOTO ErrorHandler
UPDATE tblsource SET imported = 1 where imported = 0 and notimportreason is null
ErrorHandler:
update tblsource SET notimportreason = @@error where imported = 0 and notimportreason is null
RETURN"
View 2 Replies
View Related
Mar 10, 2008
HiWe use Sql Server 2000.Is there a way to copy and entire text file into a database tablewithout using a DTS ?
View 2 Replies
View Related
Nov 1, 2007
HI,
I just copied sm data from a table in a DB to a flat file which I have created previaouly in my documents in my local machine.
I used the following command.
C:Documents and Settingsshamen> dbname.tablename out flatfile.txt -T
Once I run this it prompted two questions asking pre-fix lenght and field terminator. I jsut accepted the default values.
After I run it, it copied data. But when I see the flat file , I did not see data in that file.
To where data has been copied?
it says 10000 rows succesfully bulk-copied to host file. Total received 6176000.
But I dont see data there now.
View 5 Replies
View Related
Aug 4, 2015
Background information: SQL 2014 Ent. highly volatile OLTP environment. We generate 10 - 12 GB compressed transaction log backup files every 15 minutes.
Currently - we have two-node A/P cluster residing on flash array. Need to leverage AlwaysOn to offload processing. Replica server with have Flash storage. Replica node has same CPU and memory footprint. 10GB connection between nodes. Anyone generating such large transaction log for 15/30 minute time period?
View 0 Replies
View Related
Jul 25, 2007
Is there any simple way to copy tables from one database to another in SQL Management Studio or VS 2005? I sometimes work split work between home and work and I often need to copy and table and its data (data, stored procedure, etc) to a different database, but having to create a new database then copy the data is a pain. Is there an easier way?
View 5 Replies
View Related
Sep 2, 2002
If I have to copy data from one database to a temporary database on the same server, which transaction log will be written to as a result? Will it be the DB that the command is run from, the source DB, or possibly tempdb's log?
Thanks
Derek
View 1 Replies
View Related
Oct 31, 2004
hi all,
i have a dts that copies table contents from remore server to local server.
remote table:
ItemID|name
1 |name1
2 |name22
3 |name33
local table:
ItemID|name
1 |name1
2 |name2
i want that the dts will only copy the new rows( aka. row with ItemID = 3) and leave the other rows a they are.
can any one help me create such a dts.
please help!
View 1 Replies
View Related
May 1, 2007
Hi All
Iam using sql 2005 and am new to it, I want to copy a table and data from server to another, how is this possible?
Many Thanks in advance....
View 2 Replies
View Related
Oct 16, 2007
Hi All,
I wish to copy between 2 tables in different databases.
( The source and destiantion tables already exist)
The source table has duplicate entries which i want to avoid from being copied to destination table.
How can i achieve this?
Thanks in advance..!!
Vishu
View 20 Replies
View Related
Jul 20, 2005
I have to setup some scheduled tasks to copy 3-4 tables from anSQL2000 database to DB2 v. 8.1.The job must run every night replacing all data.How can this be done - with standard utilities??Please be exact - I'm new to this.Thank for any help./Jep
View 1 Replies
View Related
