The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Solved
MSSQL Parse XML Values Into SQL Fields
Posted on 2016-08-09
Hello...
We inherited a small project in MSSQL. The database has a field in an SQL table called "Address", which, literally, has the following sample values stored in it (6 rows follow as an example) :
Wilson Photography<br>333 54th Ave. North<br>Nashville TN 37209
Wyatt Archaelogical Research<br>4313 Green Hills Rd<br>Nashville TN 37174-5132
Ed Perdue<br>311 Hobbs Dr<br>White House TN 37188
NULL
NULL
Maury Wahtera<br>407 Normandy Circle<br>Nashville TN 37209
What we need...is to be able to have these values...parsed out into "proper" fields in a VIEW, as follows:
NAME, ADDRESS, CITY, STATE, ZIPCODE
...wherever the field value is NOT NULL (obviously :-)
We see that those values are separated by a "<br>" string, but we don't know the syntax, to parse this (XML?) value out. Any help would be very much appreciated. Thank you!...Mark
We inherited a small project in MSSQL. The database has a field in an SQL table called "Address", which, literally, has the following sample values stored in it (6 rows follow as an example) :
Wilson Photography<br>333 54th Ave. North<br>Nashville TN 37209
Wyatt Archaelogical Research<br>4313 Green Hills Rd<br>Nashville TN 37174-5132
Ed Perdue<br>311 Hobbs Dr<br>White House TN 37188
NULL
NULL
Maury Wahtera<br>407 Normandy Circle<br>Nashville TN 37209
What we need...is to be able to have these values...parsed out into "proper" fields in a VIEW, as follows:
NAME, ADDRESS, CITY, STATE, ZIPCODE
...wherever the field value is NOT NULL (obviously :-)
We see that those values are separated by a "<br>" string, but we don't know the syntax, to parse this (XML?) value out. Any help would be very much appreciated. Thank you!...Mark
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
8
7
16 Comments
Parsing strings in T-SQL is kind of a pita, unless you are using a version 13 (SQL Server 2016) database, which you could use just to do the parsing.
Using a v13 database, you can:
Wilson Photography
333 54th Ave. North
Nashville TN 37209
Using a v13 database, you can:
select * from STRING_SPLIT(replace('Wilson Photography<br>333 54th Ave. North<br>Nashville TN 37209', '<br>', '~') , '~' )
Wilson Photography
333 54th Ave. North
Nashville TN 37209
Yes, correct, 5-Columns max. Unfortunately, we are stuck with the MSSQL 2012 Iteration, and cannot go beyond that (sigh). Thoughts? Thanks!...Mark
Check this.
declare @table table (col varchar(100))
insert @table values
('Wilson Photography<br>333 54th Ave. North<br>Nashville TN 37209'),
('Wyatt Archaelogical Research<br>4313 Green Hills Rd<br>Nashville TN 37174-5132'),
('Ed Perdue<br>311 Hobbs Dr<br>White House TN 37188'),
(NULL),
(NULL),
('Maury Wahtera<br>407 Normandy Circle<br>Nashville TN 37209')
;with cte1 as (select *,charindex('<br>',col) idx1, charindex('<br>', col, charindex('<br>',col)+1) idx2 from @table),
cte2 as (select col,left(col,idx1-1) Name, substring(col,idx1+4,idx2-idx1-4) Address,substring(col,idx2+4,len(col)) str3 from cte1),
cte3 as (select *,reverse(str3) rev_str3 from cte2),
cte4 as (select *,charindex(' ',rev_str3) idx1, charindex(' ',rev_str3,charindex(' ',rev_str3)+1) idx2 from cte3)
select Name, Address,
reverse(rtrim(ltrim(substring(rev_str3,idx2,len(rev_str3))))) City,
reverse(rtrim(ltrim(substring(rev_str3,idx1,idx2-idx1)))) State,
reverse(left(rev_str3,idx1-1)) ZipCode
from cte4
/*
Name Address City State ZipCode
Wilson Photography 333 54th Ave. North Nashville TN 37209
Wyatt Archaelogical Research 4313 Green Hills Rd Nashville TN 37174-5132
Ed Perdue 311 Hobbs Dr White House TN 37188
NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL
Maury Wahtera 407 Normandy Circle Nashville TN 37209
*/
Hi Sharath...
Thank you for responding. If my Existing TableName is "NSCustomer"...and that FieldName that we want to parse into our select statement is "NSCustomer.Address"...how
Thanks!...Mark
Thank you for responding. If my Existing TableName is "NSCustomer"...and that FieldName that we want to parse into our select statement is "NSCustomer.Address"...how
Thanks!...Mark
;with cte1 as (select *,charindex('<br>',Address) idx1, charindex('<br>', Address, charindex('<br>',Address)+1) idx2 from NSCustomer),
cte2 as (select Address original_Address,left(Address,idx1-1) Name, substring(Address,idx1+4,idx2-idx1-4) Address,substring(Address,idx2+4,len(Address)) str3 from cte1),
cte3 as (select *,reverse(str3) rev_str3 from cte2),
cte4 as (select *,charindex(' ',rev_str3) idx1, charindex(' ',rev_str3,charindex(' ',rev_str3)+1) idx2 from cte3)
select Name, Address,
reverse(rtrim(ltrim(substring(rev_str3,idx2,len(rev_str3))))) City,
reverse(rtrim(ltrim(substring(rev_str3,idx1,idx2-idx1)))) State,
reverse(left(rev_str3,idx1-1)) ZipCode
from cte4
Hi Sharath...
That seemed to work beautifully...until it got to the "umpteenth" record in the NSCustomer table...and then we got the following error:
Invalid length parameter passed to the LEFT or SUBSTRING function.
How can we avoid this? Any thoughts here? The value of the FIELD is as follows, in the "next" record in the Table...and we didn't see anything "squirrely" with the data:
ArNells Art Photography<br>864 Joseph Ave.<br>Nashville TN
That seemed to work beautifully...until it got to the "umpteenth" record in the NSCustomer table...and then we got the following error:
Invalid length parameter passed to the LEFT or SUBSTRING function.
How can we avoid this? Any thoughts here? The value of the FIELD is as follows, in the "next" record in the Table...and we didn't see anything "squirrely" with the data:
ArNells Art Photography<br>864 Joseph Ave.<br>Nashville TN
You don't have ZipCode for this value. Do you have any other part (City/State) missing for any row?
Also, what is your SQL version?
Also, what is your SQL version?
Hi Sharath...
MSSQL 2012. And, yes, it's very conceivable...that ANY part of the parsed field values...may be "missing". It depends what the doggone Salespersons keyed in to the Customer's account record. There are over 45,000 records, so, yes, certainly, I'm sure "parts" may be missing. UUGH...I know,
Thoughts? And your assistance has been *very much* appreciated...Thanks!...Ma
MSSQL 2012. And, yes, it's very conceivable...that ANY part of the parsed field values...may be "missing". It depends what the doggone Salespersons keyed in to the Customer's account record. There are over 45,000 records, so, yes, certainly, I'm sure "parts" may be missing. UUGH...I know,
Thoughts? And your assistance has been *very much* appreciated...Thanks!...Ma
Is it safe assumption that the STATE is always a 2-character string?
Are these assumptions fair?
Are these assumptions fair?
- If a 2-character string is missing, that means STATE part is missing.
- If numerical string is missing, ZipCode is missing.
Hi Sharath...
Perhaps. Let me review the data...I should have an answer within the hour, OK? Thanks!...Mark
Perhaps. Let me review the data...I should have an answer within the hour, OK? Thanks!...Mark
Hi Sharath...
OK...sooooo...the data is kind of...all over the place...where "CITY", "STATE", and/or "ZIPCODE" are concerned. That's because (a) in some cases the STATE was left out of the data entry, (b) in some cases there are International Addresses and the value of 'Canada' or 'Australia' exists, (c) in some cases there are NO ZipCodes at all...and so forth.
For the *most* part, they are all "in line" with the structure of the fields we require...but probably a good 15-20% or so of the data is 'whacky'.
I think as long as the data gets parsed, somehow, someway, we can do a "data scrubbing" exercise "after the fact". Seems to ALWAYS be the case, where "Addresses" are concerned, in a Customer Master table...make sense?
So, how should we proceed? Please let me know asap...and, again, I very much appreciate the assistance...Thanks!...Mar
OK...sooooo...the data is kind of...all over the place...where "CITY", "STATE", and/or "ZIPCODE" are concerned. That's because (a) in some cases the STATE was left out of the data entry, (b) in some cases there are International Addresses and the value of 'Canada' or 'Australia' exists, (c) in some cases there are NO ZipCodes at all...and so forth.
For the *most* part, they are all "in line" with the structure of the fields we require...but probably a good 15-20% or so of the data is 'whacky'.
I think as long as the data gets parsed, somehow, someway, we can do a "data scrubbing" exercise "after the fact". Seems to ALWAYS be the case, where "Addresses" are concerned, in a Customer Master table...make sense?
So, how should we proceed? Please let me know asap...and, again, I very much appreciate the assistance...Thanks!...Mar
Featured Post
Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Course of the Month9 days, 23 hours left to enroll
609 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.