SQL Server 2012 :: Unable To Link Data After Import From XML Into Tables

Aug 13, 2015

I'm pulling data from XML into tables, but I'm unsure how to link the data after it's imported. This example has names and tasks, and I can pull the data into two tables, but I can't find any way to link the task to the appropriate person. My person and task tables populate without issue, but there's nothing I can find to link the rows together. So in this example Test 1 would go to the first two Tasks and Test 2 would go to the second two work items.

DECLARE @XML TABLE (XMLData XML);
DECLARE @Person Table (Name NVARCHAR(50), Addresss NVARCHAR(50));
DECLARE @Task Table (Name NVARCHAR(50), Details NVARCHAR(50));
INSERT INTO @XML SELECT '
<process>
<header>
<Person><Name>Test1</Name><Address>123 main street</Address></Person>

[Code] .....

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: XML Import Into Multiple Tables With Data Translation

Jul 17, 2014

I need to consume a live data feed from a golf tournament. And by consume, I really mean insert (merge) into our own SQL Server database on a regular intervals as a tournament progresses.This site didn't let me upload an XML file, but you can see a sample of the data feed here: URL....

I need to insert this data into 2 tables, Player_Holes and Player_Shots. But while doing the insert, I need to lookup several things such as our player ID match to theirs on an external_id against the players table. The shot types translation, and some other logic about the process overall.

The columns in my player_holes tables are: id, player_id, hole_id, round, shots (this is a total # of strokes) and date_created/date_modified.Shots table is similar: id, player_id, hole_id, round, shot_number, shot_type_id, club, distance, date_created/date_modified.

The only way I know how to do it, is inefficient. I would parse the XML in ColdFusion (please no comments on ColdFusion, that's what we use for webdev), and then loop over it and do inserts for each player, each hole for each round, and the shots would probably be separate for each hole.

It would be so much better and more efficient if I could do it in SQL directly. I've done some research and SQL Server Data Tools looks promising. I've never used it, so would have to learn, but also I'm not sure if that'd work in this application when we want to run is as a scheduled task every few minutes.

View 5 Replies View Related

Unable To See Data From Oracle Client Through SQL Link Server

Feb 5, 2007

Hello,

I was hoping someone had this problem and has a solution... :)

Some background info:

SQL 2005 Enterprise x64

Windows 2003 R2 x64 Enterprise (clustered)

Oracle client 10.2.0.3 (latest as of 2/2/07)

The issue I'm having is I have a link server setup within SQL 2005 connecting to a Oracle 8 database server. The Oracle client I'm using is 10.2.0.3 (latest version from Oracle). From the SQL 2005 server I'm able to query the remote oracle server through the link server I had setup if I query the table directly. If I perform a query to a view on the Oracle server I'm able to see the colum names, but not the data. This only happens when I'm using the Oracle client, if I use the MS client for Oracle it works fine OR if I use SQL Plus.

Now, you're probally asking, why not just use MS's driver... Well, I would If I could, but Microsoft hasn't provided a 64 bit driver for Oracle which I need to create the link server in SQL.

Any help would be appreciated.

View 4 Replies View Related

SQL Server 2012 :: Find Common Link Between Two Tables In Database Without Key Constraints?

Jul 17, 2015

CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )

What is the link between these two tables?

I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."

Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.

View 2 Replies View Related

Cannot Import/link Data From DBase With SQL Server DTS Nor Access Anymore..

Jul 20, 2005

Hi,SQL Server 2000 SP3Windos 2000 Server SP4I have a DTS package that imports data from a dBase IV databse withfiles located in two folders (dBF1 and dBF2). I use a transform datatask to transform the data.They were running properly, but last week we installed W2K SP4, andnow the transform task for files from dBF2 are not working properly.I have two tranform tasks to extract data from files in dBF2 folder.If I double click to open the transform data task of either of them,Enterprise Manager crashes with the errrormmc.exe applicatio ErrorThe instruction "xxxx" referenced memory at "xxx". The memory couldnot be read.Althoug the transform task for one of the files will run, the otherwill not run giving the messageError Source: DTS packageCatastrophic failureAlso, I have an Access database that has links to the same dBasefiles. For files from dBF2 folder, I'm able to see the data from oneof the files, but if I double click to see the data from the other,access crashes with no specific error message. Nothing has changed ondBase related files (permission wise).The transform tasks to extract data from the other dBase folder (dBF1)files are working fine, and data is accesible from Access.Any advice how to tackle this one?

View 2 Replies View Related

SQL 2012 :: Data Import From Teradata To Server

Jun 9, 2015

I am fetching large amount of data from teradata to sql server using linked server. I am facing below query:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

View 0 Replies View Related

Unable To Download SQL Server Express. Bad Link?

Apr 29, 2008

Hi all,

I'm trying to download a copy of SQL Server Express 2005 from the Microsoft website at the following link:

http://www.microsoft.com/sql/downloads/trial-software.mspx#EZC

however I keep getting a page displaying a "Content Not Found" message. Would anyone be able to tell me is this the correct page or should I be downloading this from someplace esle?

Also does the Express edition contain a copy of Enterprise Manager?

Any help would be greatly appreciated.

Thanks!

-Shannon

View 3 Replies View Related

SQL Server 2012 :: Import Data From HTML Document Into Table

Oct 8, 2014

I have someone who is sending me .htm documents, with a table in them, and I was wondering if there is a way to import the data from those tables into a SQL table, probably using an SSIS Package.

View 4 Replies View Related

SQL Server 2012 :: CSV Import With Non-character Data (Date And Decimal)

Mar 29, 2015

I'm using OpenRowSet to import about 30 columns from a csv file with 190 columns with a format file. Ultimately, I want to put this in an SSIS Package. I am receiving the following error when trying to import date and decimal info.

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 990, column 64 (TOTALSALES). There are several similar errors. I looked at this line and it is 17873.34 so I am not seeing the problem. Every value in the column is either 0 or a 2 digit decimal value. If I change the SQL Column and format file to to NVARCHAR, it imports fine.

The existing format file and SQL Column looks as follows. There are multiple errors referring to different columns and all of them seem to be valid decimals. I am having the same issue with date fields that exist in the csv as 20130521. If I bring it in as text, it is fine.

<FIELD COLLATION="SQL_Latin1_General_CP1_CI_AS" MAX_LENGTH="12" TERMINATOR="," xsi:type="CharTerm" ID="64"/>
<COLUMN xsi:type="SQLDECIMAL" SCALE="2" PRECISION="15" NAME="TOTALSALES" SOURCE="64"/>

The SQL Column is defined as Decimal ((15,2), NULL))

I created a small csv file with representative decimal, date, integer and NVarchar fields and it imports into SQL fine as decimal and date info. The SQL Query used is pretty simple. Ultimately, I am planning to create a package that imports this data and joins to a production table based on values in the csv file. It will either update existing values in a Production Table or insert New Values

INSERT INTO Import.dbo.test1
SELECT *
FROM OPENROWSET(BULK 'C:ShareImport.csv',
FirstRow=2,
FORMATFILE='C:ShareImport.xml'
) AS t1;

I am assuming there is bad data in the csv file but I'm not sure how to identify it as my test file seems to bring in date values with a format of 20140923 and 2 digit decimal values and that is what exists in the line numbers being referenced. I've not used OpenRow Set for this purpose before. The only workaround I've found is to bring it all in as text and create additional fields so I can cast or convert the date values which I'd rather not do as this process seems to work in my small sample file.

View 1 Replies View Related

DB Engine :: Server 2012 Import Data From Excel 2010

Oct 11, 2015

I used sql server 2012 express Import and Export Data (32-bit) wizard to import data from excel 2010 to a given table. But I got the following error message: Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unspecified error". (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with Destination - MPRecord.Inputs[Destination Input].Columns[Top1] on Destination - MPRecord.Inputs[Destination Input]. The column status returned was: "The value violated the integrity constraints
for the column.". (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination - MPRecord.Inputs[Destination Input]" failed because error code 0xC020907D occurred, and the error row disposition on "Destination - MPRecord.Inputs[Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - MPRecord" (35) failed with error code 0xC0209029 while processing input "Destination Input" (48). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

View 2 Replies View Related

Access2003 Cannot Link (but Excel2003 Can Link) To Tables In Access97 Database

Feb 20, 2008

I have created an Access2003 project (existing data) that links to external data. First I connected to a SQL Server 2000 database. Success. Then I tried to set up a Transact SQL data connection to a legacy MDW-secured Access97 database. (A third-party VB6 application goes against it, and we don't have the source code, so we cannot upgrade it.)

The Transact SQL link tests OK but I cannot select any of the tables or queries from the list presented. However, with the same credentials, I can use these same objects in Excel 2003.

When setting up the link in Access2003, I specify JET 4.0 OLE DB Provider, I enter the MDW file on the All tab, a username and a password on the Connection tab where I browse to the MDB file, and specify Shared Deny None on the Advanced tab. When I test the connection, it tests OK ("Test connection succeeded"). Yet on the "Select the Database and Table/Cube which contains the data you want" dialog, "(Default)" appears in the grayed-out dropdown. Then, beneath that dropdown, there is a grid with Name and Description columns. The grid contains query names but the grid is not enabled. The list of queries is this table is grayed out. Neither of the scrollbars works.

BUT... if I use the SAME username and password in Excel2003, and specify the same MDW, there is no problem working with these same database objects in the legacy Access97 database. WHAT IS DIFFERENT ABOUT THE WIZARD IN EXCEL THAT ALLOWS IT TO SUCCEED AND THE WIZARD IN ACCESS THAT CAUSES IT TO FAIL HERE? In Excel, the list of available providers says Microsoft Access Driver, not JET 4.0 OLE DB Provider.

Thanks
TR

View 1 Replies View Related

Cannot Link To SQL Tables Using ODBC Link Table In Access 2003

Feb 3, 2006

When trying to link to an SQL table in Access 2003, the software appears to be malfunctioning. 

The sequence of events is File - Get External Data - Link Tables - Files of Type: ODBC Databases().

The Problem: On two of my computers, the select data source window does not pop up, preventing me from linking to any ODBC data source. 

Observations:  This function has worked normally in the recent past and works on other computers running Access 2003.  One difference between the computers working and non-working computers is Norton Antivirus 2006 (recent upgrade).

Has anyone experienced anything like this?  What's going on?

View 8 Replies View Related

SQL Server 2012 :: Unable To Load Data From Flat File To Table Using Bulk Insert Statement

Apr 3, 2015

I am unable to load data from flat file to sql table using bulk insert sql statement

My code:-

DECLARE @filePath VARCHAR(200)
DECLARE @sql VARCHAR(8000)
Declare @filename varchar(100)
set @filename='CCNVZ_150401054418'
SET @filePath = 'I:IncomingFiles'+@FileName+'.txt'

[Code] .....

View 1 Replies View Related

SQL 2012 :: Unable To Export Tables From GIS Application To The Database

Oct 1, 2014

I am using SQL server 2012. An user tried to export tables from GIS application to the SQL database.

After export, and login to the SQL server, we see all the tables has his name as the schema but not dbo.

He was added as a login and user as in a windows group. Meaning he is a member of the windows group.

I assume when export, the default schema should be dbo. but apparently not.
I went to the setting and explicitly make the default schema for the windows group user to dbo. But he tried again, it still use his username as schema prefix to the tables. just wondering why is this?

View 7 Replies View Related

How To Link Mssql Data (tables) In Notepad (HTML)

Nov 29, 2006

menardo writes "Hi there!
I'm a very newbie in SQL programming. Can anybody help me how do I start to learn scriptings.

Can you give me the process or scripts on how to export it??

Thanks in advance.

-menardo"

View 2 Replies View Related

Sql Or Link To Import Cells/ranges (excel 2003) To Sql Server 2005

Feb 27, 2008

I have around 600 worksheets that i need to import into sql server that are in a somewhat non-table like format. Data defractor seems to be able to do what i need, but i also need to check into doing it manually. I've seen the code to import cells and ranges into sql server, but can't seem to remember what it is anymore. I've also searched through the forums and can't seem to find any examples either. Could anyone post an example or a link with some examples or explaination code. Thanks in advance.

View 4 Replies View Related

SQL 2012 :: Import Visual FoxPro Tables Into Database

Aug 7, 2014

How can I add the option to import data from Visual FoxPro tables into SQL 2012 database?

View 0 Replies View Related

SQL Server 2012 :: Merge Data From Two Tables

Feb 3, 2014

Merging table :

--------Dummy TABLE

create table #Tbl1 (date1 date,WSH varchar(10),ITN int,Executions int)
insert into #Tbl1 (date1 , WSH , ITN , Executions)
select '20130202' ,'ABC', 1 , 100
union all
select '20130203' ,'DEF', 1 , 200

[Code] .....

I want Result like this :

date1 WSH ITNExecutionsMCGPositions
2013-02-02 ABC 1 100 2 500
2013-02-03 DEF1 200 NULL NULL
2013-02-05 NULLNULL NULL 2 600

View 2 Replies View Related

SQL Server 2012 :: Copying Data From One Set Of Tables To Another

Mar 26, 2014

1. Take a subset of data from about 100 tables that have multiple references to other tables in this group of 100 from a first DB.
2. Insert the above data into a second DB, a database that already has data in the 100 tables, while maintaining the correct references.

As a general approach, the best way I can think of doing this is as follows:

1. Create mapping tables for every ID that is referenced in a different table (OldID NewID)
2. Insert the old data into the new table and output the OldID and NewID into the mapping table.
3. Use that mapping data to make sure all tables that use those IDs have the new IDs in DB2.

This approach is extremely labor intensive both on initial implementation and would require a fairly substantial amount of work to maintain going forward.

View 7 Replies View Related

SQL Server 2012 :: How To Combine Data From Different Tables Using A Query

Dec 17, 2013

I have a set of tables that look like what I have shown below. How I can achieve the desired output ?

CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);

INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);

[Code] ....

I have a query currently that looks like this . @Month and @Year are supplied as parameters

SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]

[Code] ...

Right now I see an output like this : for a particular value of @Month and @Year

SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :

[Customer],SalesofProductA, SalesofProductB, SalesofProductC

How it can be done ?

View 2 Replies View Related

SQL Server 2012 :: Query To Consolidate Data From Tables

Feb 12, 2014

I need to make a query that counts installed developer software for all our developers (from the sccm database), for licensing purposes. The trick here is that a license should only be counted once per. developer and that should be the highest version. But in the database, the developers can have different versions of the software installed (upgrades) on the same computer and they often use several computers with different software versions.

So for example: A source table with two developers

-------------------------------------------------------------------
| dev1 | comp1 | Microsoft Visual Studio Ultimate 2013
| dev1 | comp1 | Microsoft Visual Studio Professional 2010
| dev1 | comp2 | Microsoft Visual Studio Premium 2010
| dev2 | comp3 | Microsoft Visual Studio Professional 2010
| dev2 | comp4 | Microsoft Visual Studio Premium 2012
--------------------------------------------------------------------

I want the result to be:
-----------------------------------------------------
| dev1 | Microsoft Visual Studio Ultimate 2013
| dev2 | Microsoft Visual Studio Premium 2012
------------------------------------------------------

I have created a query using cursors that give me the correct result, but it's way to slow to be acceptable (over 20 min..). I also toyed with the idea of creating some sort of CRL proc or function in C# that does the logic, but a SCCM consultant from MS said that if I create any kind of custom objects on the SCCM SQL Server instance, we loose all support from them. So I'm basically stuck with using good old fashioned T-SQL queries.

My idea now, is to use a CTE table and combine it with a Temp table with the software and a rank. I feel that I'm on the right track, but I just can't nail it properly.

This is how far I have come now:

IF OBJECT_ID('tempdb..#swRank') IS NULL CREATE TABLE #swRank(rankID int NOT NULL UNIQUE, vsVersion nvarchar(255))
INSERT INTO #swRank(rankID, vsVersion)
VALUES
(1, 'Microsoft Visual Studio Ultimate 2013'),
(2, 'Microsoft Visual Studio Ultimate 2012'),

[Code] ....

View 4 Replies View Related

SQL Server 2012 :: Removing / Deleting Data From 2 Tables

Mar 11, 2015

I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -

SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml

[code]...

View 2 Replies View Related

Link MS SQL Server 2000 Tables

Mar 18, 2004

Hello All,

I am new to this forum and hope that some one can help me with this problem.

I know that in MS Access you can have a linked table in your databse such that as long a the like is not broken, it behaves similar to any other table in the database.



My problem is that I need to do the same thing for multiple databases that I have on a MS SQL Server that ALL currently have copies of the same table in common.

I wish to have a single table that they all share and have access to just link any other table that they currently have but is actually linked.

Can this be done?

And if so, then could someone please guide me in this?

My logic is that if MS Access can link tables then I could suspect that MS SQL Server should be able to also, but I could be wrong.

Thanks in advance,
Lonnie

View 5 Replies View Related

SQL Server 2012 :: Open EDI File And Insert Data Into Tables

Sep 27, 2012

Is there a way to open EDI file in SQL Server and insert the data into tables?

View 9 Replies View Related

SQL Server 2012 :: Select Large Data From Multiple Tables

May 10, 2014

In a Library Management database we have these tables

1) Document ( DocNo , Doc_type , permalink,inDate)
2)Title(id, DocNo,Main_Title, Other_Title)
3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....)
4)Publisher(id,DocNo , Name,Publisedate,address)
5)Subject(id,DocNo,Subject)
6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc

In document table I have 500,000 records.

I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?

View 3 Replies View Related

SQL Server 2012 :: Purging Data From Master And 5 Child Tables

Jul 5, 2014

I have 6 tables which are very huge in row count and records needs to deleted which are older than 8 days.

Little info: Every day, 300 Million records are inserted in below 7 tables. we should maintain only 8 days worth of data in below tables. How to implement Purge script which can delete records in all tables in the same time and with optimized parallelism.

Master table which has [ID],[Timestamp]
Table Name: Sample - 2,578,106

Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table. So the records needs to deleted based on Min(ID) from Sample

dbo.ConnectionDB - 1,147,578,048
dbo.ConnectionSS - 876,458,321
dbo.ConnectionRT - 118,133,857
dbo.ConnectionSample - 100,038,535
dbo.Command - 100,032,235

View 9 Replies View Related

SQL Server 2012 :: How To Loop Unmatched Rows Data From Two Tables

Jan 12, 2015

We are having folder table and team table as like below structure.

Folderlist (F)Table: (
==============

id ,folder_name, parent_id
1, c, 101
2,b,202
3,c,203

Teamlist table (T)
============

team_id, Team_name, Parent_folderid
101 , mobile,101
202 ,Tab,200
200, Phone,200
203,apple,205
205,nokia,208
208,samsung,208

If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101)
then output should come as 'Mobile/c' (this is for f.parent_id=101)

If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id
then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id

Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache

like phone/tab/b (this is for F.parent_id=202)

View 1 Replies View Related

How To Link SQL Server Tables To Access Or Oracle

Mar 7, 2000

Hi All

My manager told me to link SQL Server database tables to access so that he can access the tables in MSAccess to do his SQL queries.
I am thinking of linking server but I am not sure about that.
Is someone can tell me what to do and explain me how.
Thanks in advance
Sincerely.
David

View 2 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

SQL Server 2012 :: How To Merge Two Tables Data On Behalf Of ID In Comma Separated Manner

Jul 2, 2015

i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012.

This is the image path :

[URL]

View 3 Replies View Related

Link SQL Server 2005 Tables To External Database Through ODBC

Sep 24, 2007

Hi
I'm a newbie at SQL 2005 and I'm trying to create linked tables to our ERP system through ODBC. I can do this in MS Access or vb.net by using the ERP system's ODBC driver, but I am lost when it comes to SQL Server 2005.
Thanks for any help

View 1 Replies View Related

Import XML Data To SQL Tables

Apr 2, 2008

Hi, I am trying to import an XML file to SQL tables using SSIS, Data Flow Task --> XML Source --> OLEDB destination.
I have both the XML file and its xsd file.

The extraction of the XML file output 5 tables (mc, r, i, bc and c, have relationship among them). My problem is that one of the tables is empty (table mc). I tried to add a "root" node to the XML file (the xsd file was modified too), and all 5 tables were populated correctly. Is there any other way to fix this problem without manually changing the XML file?

The XML file looks like this:

<mc MajCmd="Atlantic">
<r Region="East">
<i Installation_UIC="M00146" Installation_Name="Cherry Point">
<bc BC="111" BC_Title="Airways">
<c Restoration_Cost_C3="0.0000" Comment_Type="" Override_C_Rating="" Comment="" />
</bc>
</i>
</r>
</mc>

The xsd file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
<xs:element name="mc">
<xs:complexType>
<xsequence>
<xs:element name="r" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsequence>
<xs:element name="i" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsequence>
<xs:element name="bc" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsequence>
<xs:element name="c" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
</xs:complexType>
</xs:element>
..........
</xsequence>
<xs:attribute name="BC" type="xstring" />
<xs:attribute name="BC_Title" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="Installation_UIC" type="xstring" />
<xs:attribute name="Installation_Name" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="Region" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
<xs:attribute name="MajCmd" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element ref="mc" />
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>


Thanks!
ChaInn

View 2 Replies View Related

SQL Server 2014 :: Find Common Link Between Two Tables In Database Without Key Constraints

Jul 16, 2015

Any tool, script, procedure, or otherwise that will solve this problem?

CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )

CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )

What is the link between these two tables?

I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."

Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.

View 7 Replies View Related







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