We have a project to parse out an xml file into relational sql table. The xml file is complex type with multiple nesting. We are trying to resort to use XQuery to parse it out to SQL tables-because of one thing or the other - other options on the table were not viable. I know that we can use C# to do the same thing but we are sticking to TSQL with Xquery. Has anybody used the same route for processing large complex xml files?
I am trying to parse data separated through text (ie abc1, abc2, abc3, abc4, etc).
ID ParseData 1 [abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.] 2 [abc1.Gray Shoes/abc2.Striped jacket /abc3./abc4./abc5./abc6./abc7./abc8.] 3 [abc1.Blue jeans/abc2./abc3./abc4./abc5./abc6./abc7./abc8.]
New Data (abc1, abc2, abc3, etc each have a field in the new data set) ID ParseData abc1 abc2 abc3 abc4 abc5 abc6 abc7 abc8 1 [abc1.Pants...abc8.] Pants Orange hat Purple shirt 2 [abc1.Gray...abc8.] Gray Shoes Striped jacket 3 [abc1.Blue...abc8.] Blue Jeans
If I only want the data in between abc1 and abc2, between abc2 and abc3, etc, what would be the best way to do that?
My code so far looks like: DECLARE @string varchar(100) = '[abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]', @searchString1 varchar(20) = 'abc1', @searchString2 varchar(20) = 'abc2';
SELECT newstring FROM dbo.SubstringBetween(@string,@searchString1,@searchString2);
This returns 'Pants.'How do I continue to parse between abc2 and abc3? between abc3 and abc4?And then continue to ID2?Should I be referencing the ParseData field instead of string of data that I want to parse?
Please let me know if you come across any name strings this function cannot parse. CREATE function FormatName(@NameString varchar(100), @NameFormat varchar(20)) returns varchar(100) as begin --blindman, 11/04 --FormatName parses a NameString into its component parts and returns it in a requested format. -- --@NameString is the raw value to be parsed. --@NameFormat is a string that defines the output format. Each letter in the string represents --a component of the name in the order that it is to be returned. --[H] = Full honorific --[h] = Abbreviated honorific --[F] = First name --[f] = First initial --[M] = Middle name --[m] = Middle initial --[L] = Last name --[l] = Last initial --[S] = Full suffix --[s] = Abbreviated suffix --[.] = Period --[,] = Comma --[ ] = Space
--Example: select dbo.Formatname('Reverend Gregory Robert Von Finzer Junior', 'L, h. F m. s.') --Result: 'Von Finzer, Rev. Gregory R. Jr.'
--Test variables -- declare@NameString varchar(50) -- declare@NameFormat varchar(20) -- set@NameFormat = 'L, h. F m. s.' -- set@NameString = 'Reverend Gregory Robert Von Finzer Junior'
--Prepare the string --Make sure each period is followed by a space character. set@NameString = rtrim(ltrim(replace(@NameString, '.', '. '))) --Eliminate double-spaces. while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ') --Eliminate periods while charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')
--If the lastname is listed first, strip it off. set@TempString = rtrim(left(@NameString, charindex(' ', @NameString))) if@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @TempString = rtrim(left(@NameString, charindex(' ', @NameString, len(@TempString)+2))) ifright(@TempString, 1) = ',' set @LastName = left(@TempString, len(@TempString)-1) iflen(@LastName) > 0 set@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
--Get rid of any remaining commas while charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')
--Get Honorific and strip it out of the string set@TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' '))) if@TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'CPL', 'Corporal', 'SGT', 'Sergeant', 'GEN', 'General', 'CMD', 'Commander', 'CPT', 'CAPT', 'Captain', 'MAJ', 'Major', 'PVT', 'Private', 'LT', 'Lieutenant', 'FATHER', 'SISTER') set @Honorific = @TempString iflen(@Honorific) > 0 set@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
--Get Suffix and strip it out of the string set@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if@TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @Suffix = @TempString iflen(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
if @LastName is null begin --Get LastName and strip it out of the string set@LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) set@NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName))) --Check to see if the last name has two parts set@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') begin set @LastName = @TempString + ' ' + @LastName set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString))) end end
--Get FirstName and strip it out of the string set@FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' '))) set@NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))
--Anything remaining is MiddleName set@MiddleName = @NameString
--Create the output string set@TempString = '' while len(@NameFormat) > 0 begin if @IgnorePeriod = 'F' or left(@NameFormat, 1) <> '.' begin set @IgnorePeriod = 'F' set @TempString = @TempString + case ascii(left(@NameFormat, 1)) when '72' then case @Honorific when 'Dr' then 'Doctor' when 'Rev' then 'Reverend' when 'Hon' then 'Honorable' when 'Maj' then 'Major' when 'Pvt' then 'Private' when 'Lt' then 'Lieutenant' when 'Capt' then 'Captain' when 'Cpt' then 'Captain' when 'Cmd' then 'Commander' when 'Gen' then 'General' when 'Sgt' then 'Sergeant' when 'Cpl' then 'Corporal' else isnull(@Honorific, '') end when '70' then isnull(@FirstName, '') when '77' then isnull(@MiddleName, '') when '76' then isnull(@LastName, '') when '83' then case @Suffix when 'Jr' then 'Junior' when 'Sr' then 'Senior' when 'Esq' then 'Esquire' else isnull(@Suffix, '') end when '104' then case @Honorific when 'Doctor' then 'Dr' when 'Reverend' then 'Rev' when 'Honorable' then 'Hon' when 'Major' then 'Maj' when 'Private' then 'Pvt' when 'Lieutenant' then 'Lt' when 'Captain' then 'Capt' when 'Cpt' then 'Capt' when 'Commander' then 'Cmd' when 'General' then 'Gen' when 'Sergeant' then 'Sgt' when 'Corporal' then 'Cpl' else isnull(@Honorific, '') end when '102' then isnull(left(@FirstName, 1), '') when '109' then isnull(left(@MiddleName, 1), '') when '108' then isnull(left(@LastName, 1), '') when '115' then case @Suffix when 'Junior' then 'Jr' when 'Senior' then 'Sr' when 'Esquire' then 'Esq' else isnull(@Suffix, '') end when '46' then case right(@TempString, 1) when ' ' then '' else '.' end when '44' then case right(@TempString, 1) when ' ' then '' else ',' end when '32' then case right(@TempString, 1) when ' ' then '' else ' ' end else '' end if ((ascii(left(@NameFormat, 1)) = 72 and @Honorific in ('FATHER', 'SISTER')) or (ascii(left(@NameFormat, 1)) = 115 and @Suffix in ('II', 'III'))) set @IgnorePeriod = 'T' end set @NameFormat = right(@NameFormat, len(@NameFormat) - 1) end
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
Need getting data into XML format as shown in the last code block.The datatypes and table structures are pretty much fixed, but I can re-hash data into another Temp Table, CTE, etc..This is a server running SQL Server 2012, but I'd guess any version that understands FOR XML PATH should be fine.
Source tables and data if object_id('Tempdb..#Element1') is not null drop table #Element1; create table #Element1 ( [Attr1] varchar(10) ,[Attr2] varchar(4)
I am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date, and if the inventory is "checked out" using the ID name of the person who has the item.
For example it would look like this:
-------------------------------------------------------------------------------------------------- Inventory Name | 10/24/2015 | 10/25/2015 | 10/26/2015 | 10/27/2015 | 10/28/2015 -------------------------------------------------------------------------------------------------- Laptop | Tom | Tom | Tom | Avail | Avail Projector | Avail | Avail | Avail | Avail | Bob Air Card | Bob | Bob | Bob | Bob | Bob
It seems like I want to do a pivot table but there really is no aggregate so I am not sure what to use.
Why am I getting message "A valid table name is required for in, out, or format options."
I used the syntax from a tutorial about bcp utility. I am trying to create a format file for flat file import and export.
My server instance is "stat-hpsqlexpress"
The database name is "STATRLO"
Owner is "dbo"
Table name is "PM-allactivity-emaillog_042315"
The bcp comand I am trying to run is:
bcp STATRLO.dbo.PM-allactivity-emaillog_042315 format nul -c -t, -f C:databaseActivity_c.fmt -S stat-hpsqlexpress - T
Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. SQL Server Version: Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64) Jul 22 2014 15:26:36 Copyright (c) Microsoft Corporation Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Yes I know the instance says sqlexpress...it was upgraded.
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
Hi I am trying to export an table data to csv format. The problem here is the table columns are dynamic. The DTS exports only the columns available during the DTS design time and it ignores if any new columns are added after the design. I need solution for this asap. Thanks SqlJerin
I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?
Hello,I have a problem the scenario is :I have data in an excel file and now I am reading data from that file and insert that data into sql database. this is well.but the problem is that I have few fields with date time data in excel sheet. In my database I have varchar type data type for these data columns.I want to read these data columns from the excel sheet and insert only time into the data base.how can I do this I am using the following line of code for selelcting only time from the excel file. string qry = "Select CONVERT(CHAR(5),datetime,114) from [" + objStr[0] + "];";this gives me error message.help me to read the data from excel file and insert it into the sql table in desired format. Thanks in advance, junior
I uploaded custtable under the database, the data looks fine except that the name that apprears has a lot of distance e.g
it should be :
firstname lastname however the format appears very strange:
firstname lastname
firstname lastname
fistname lastname
Same is the case with the address, I need to adjust or format the apperance that appears on the cell. Is there a way/ sql statement to format the data under the table so that the apprearence looks okay.
I will really appreciate any sort of help on this one.
The data looks like the following --------------------------- | PBP 20070420 2:26pm | ---------------------------
Now the data in this field is not uniform it can be blank, a sentence or have a different pre fix, instead of PBP, but the date will be YYYYMMDD when it is supplied.
I need to find all the dates that are within the last 10 months. How do I perform this task?
I have just inherited a new project consisting of data imported into sql 2005 from a multi-dimensional database. After finding the correct ODBC and importing the data I believed that I was done, but after reviewing the resulting structure I discovered why this was called a €œmulti-dimensional€? database. The resulting imported data is completely de-normalized and resembles an excel spreadsheet more than a relational database. An example of this structure is the persons table. The table has multiple columns, some of which contain the multi-dimensional fields. These fields contain multiple values which are separated with a tilde, €œ~€?. I need to parse out the data and normalize it. In the specific sample of data below I attempting to take the personid, associates, and assocattrib and insert them into a sql table, associates. This table references the persons table where the personid and the associates references the personid in the persons table.
CREATE TABLE [dbo].[associates]( [associd] [int] NOT NULL REFERENCES persons(personid), [namepkey] [int] NOT NULL REFERENCES persons(personid), [assocattribute] [varchar](20) NULL )
The purpose of normalizing this data will be to show the realationship(s) between people as it has been documented in the previous data structure, i.e. person 1 is an associate of person 336 and the attribute is WIT.
My problem lies in attempting to parse out the associates and assocattrib columns and relate them to the appropriate personid. The personid relates to each associate and assocattrib and the tilde, ~, separates the values ordinal position which, in sql, would be separate rows. For example the single row: personid associates assocattrib 58201 252427~252427~252427 VICT/SUSP~WIT~RP Should be: 58201 252427 VICT/SUSP 58201 252427 WIT 58201 252427 RP
The imported data can have no associates: personid associates assocattrib 152683 NULL NULL
or up to 69 associates, I am not even going to try to paste all of that here.
There are over 400,000 rows that I am looking at parsing, so any way of doing this in t-sql or the clr would be GREAT. This data is stored in SQL 2005 standard SP2. The specific machine is our test/reporting server, so I am not necessarily concerned with the best performing solution, I am leaning more towards providing some free time for me.
Any help or suggestions, including better ideas on the table structure, would be greatly appreciated.
I have several bcp output files I need to import into tables. I do not have format files for them. As far as I know they are in native format. I do not know the layout of the destination table they would populate.
1) how can I determine from the bcp file itself the schema of the destination table? Once I know that I should be able to import the data into the table.
I am debugging a DB maintance script which creates a table of index maintainance commands which are created separately for each index according to the level of fragmentation and other factors.
For the debugging process, I'm looking for a way to parse each command in the table without actually running them to locate any syntax errors. In other words, as if you clicked the blue check on each one.
Does such a function exist in SQL 2008 (the version I'm doing this on) or other versions?
We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.
Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.
I have a third party application with a ntext field that I need to parse the data out of. The data looks like this: <xmlF><FNumber type="int">2421</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">John Smith</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate</xmlF> The fieldname is Data and the tablename is ProcessData Again, this looks like xml, but the field type is ntext. I would like to create a view displaying the parsed data in fields. How would I go about parsing the data? Thanks.
SP to parse a delimited string and insert the result in a table. I am using SQL Server 2008 R2. I have 2 tables - RawData & WIP. I have Robots on a manufacturing line capable of moving data to a DB. I move the raw data to RawData. On insert [RawData], I want to parse the string and move the contents to WIP as indicated below. I will run reports against the WIP Table.
Also, after the string is parsed, I'd like to change the Archive column, the _0 at the end of the raw string to 1 in the WIP table to indicate a successful parse.
Date Time Plant Program Line Zone Station BadgeID Message Alarm Archive ----------------------------------------------------------------------------------- 04102015 114830 10 13 9 8 6 99999 Test 1 1 1 04102015 115030 10 13 9 8 6 99999 Test 2 1 1
What is the best way to transfer data from the staging table into the main table.
Example: Staging Table Name: TableA_satge (# of rows - millions) Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing: - Load data into staging table (TableA_stage) - Remove any duplication of rows from the staging table (TableA_stage) - Disable all indexes on main table (TableA_main) - Insert into main table (TableA_main) from staging table (TableA_stage) - Remove any duplication of rows from the main table using CTE (TableA_main) - Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, My understanding at least is that I need to perform a bulk insert.
Example XML file:
<?xml version="1.0" encoding="ISO-8859-1"?> <!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd"> <Asset_Collection> <Collection_Metadata Name="Asset Collection" Description="Random XML Feed Test"
[Code] ....
Table/Columns which need to be inserting into: Table: TABLE_A
