RE: sqlparser parseresult - How do you parse?

SSC Guru

Points: 323504

July 1, 2011 at 11:48 am

Babu thanks for giving me something new and interesting to play with; being able to parse a statement has a lot of possibilities.

i found an example in german here: http://olafhelper.over-blog.de/article-34700638.html

i created a simple buttona dn put this specific code underneath it.

what you want to look at is the tokens...i see how different tokens are used to identify columns, tables, even whitespace and comments, everything in the query is chopped up into something.

i see so many possibilities witht his...wow.

Private Sub btnParse_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParse.Click

Dim sql As String

Dim script As Microsoft.SqlServer.SqlParser.SqlCodeDom.SqlScript

Dim stringBuilder As New System.Text.StringBuilder

Dim token As Microsoft.SqlServer.SqlParser.Parser.Token

sql = "-- Erstes Statement völlig Valide, aber mies formatiert " & vbNewLine & _

"select [AddressID] " & _

", [AddressLine1] " & _

",[AddressLine2] " & _

", [City] " & _

"fRoM [AdventureWorks].[Person].[Address] " & _

"whERe PostalCode = '98011';" & vbNewLine & _

"GO;" & vbNewLine & _

"-- Zweites Statement: So strunz fehlerhaft " & vbNewLine & _

"-- das es schon weh tut; Format auch nicht besser " & vbNewLine & _

"select * " & _

"FRoM TabelleA oder TabelleB " & _

"where Ja = Vielleicht <> Nein;"

sql = " " & vbCrLf

sql = sql & "SELECT e.EmployeeID, c.Title, c.FirstName, c.MiddleName, c.LastName, c.Suffix, e.Title AS JobTitle, c.Phone, c.EmailAddress, c.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, cr.Name AS CountryRegionName, c.AdditionalContactInfo " & vbCrLf

sql = sql & "FROM HumanResources.Employee AS e INNER JOIN " & vbCrLf

sql = sql & "Person.Contact AS c ON c.ContactID = e.ContactID INNER JOIN " & vbCrLf

sql = sql & "HumanResources.EmployeeAddress AS ea ON e.EmployeeID = ea.EmployeeID INNER JOIN " & vbCrLf

sql = sql & "Person.Address AS a ON ea.AddressID = a.AddressID INNER JOIN " & vbCrLf

sql = sql & "Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN " & vbCrLf

sql = sql & "Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode " & vbCrLf

script = Microsoft.SqlServer.SqlParser.Parser.Parser.Parse(sql)

System.Console.WriteLine("Anzahl Batches: " & script.Batches.Count.ToString)

System.Console.WriteLine("Anzahl Fehler: " & script.Errors.Count.ToString)

For Each token In script.Tokens

'Debug.Print("token ID [" & token.Id & "] ;Token Type [" & token.Type & "]" & " xml [" & token.Xml & "]")

Debug.Print("token xml [" & token.Xml & "]")

Select Case token.Id

Case 40, 41, 59, 61

'As separators () =, which may be without Spaces

stringBuilder.Append(token.Text.Trim)

Case 42, 171

'Fields, the only trim

stringBuilder.Append(token.Text.Trim)

Case 44, 498 '498 = "LEX_WHITE", vbCrLf i think

'Empty field, where only max. a Space

stringBuilder.Append(token.Text.Trim & " ")

Case 242, 287, 307 '287 = "TOKEN_SELECT"

'SQL clauses wie SELECT FROM WHERE

'The next in line as UPPER

stringBuilder.Append(vbNewLine & token.Text.ToUpper(System.Globalization.CultureInfo.CurrentCulture))

Case 499 'Type: "LEX_END_OF_LINE_COMMENT"

'Comments, and no unnecessary spaces in new row

stringBuilder.Append(vbNewLine & token.Text.Trim.Replace(" ", " "))

Case 501

'GO batch separators, always in new row

stringBuilder.Append(vbNewLine & token.Text.Trim)

Case Else

'Everything else return unchanged

stringBuilder.Append(token.Text)

End Select

Next

'System.Console.WriteLine(stringBuilder.ToString)

'System.Console.WriteLine("Press key to continue")

'System.Console.ReadLine()

End Sub

anyway, for reference, your specific Adventureworks is tokenized into this:

token xml [<Token location="((1,1), (2,1))" id="498" type="LEX_WHITE"> \r</Token>]

token xml [<Token location="((2,1), (2,7))" id="287" type="TOKEN_SELECT">SELECT</Token>]

token xml [<Token location="((2,7), (2,8))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,8), (2,9))" id="171" type="TOKEN_ID">e</Token>]

token xml [<Token location="((2,9), (2,10))" id="46" type=".">.</Token>]

token xml [<Token location="((2,10), (2,20))" id="171" type="TOKEN_ID">EmployeeID</Token>]

token xml [<Token location="((2,20), (2,21))" id="44" type=",">,</Token>]

token xml [<Token location="((2,21), (2,22))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,22), (2,23))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((2,23), (2,24))" id="46" type=".">.</Token>]

token xml [<Token location="((2,24), (2,29))" id="171" type="TOKEN_ID">Title</Token>]

token xml [<Token location="((2,29), (2,30))" id="44" type=",">,</Token>]

token xml [<Token location="((2,30), (2,31))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,31), (2,32))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((2,32), (2,33))" id="46" type=".">.</Token>]

token xml [<Token location="((2,33), (2,42))" id="171" type="TOKEN_ID">FirstName</Token>]

token xml [<Token location="((2,42), (2,43))" id="44" type=",">,</Token>]

token xml [<Token location="((2,43), (2,44))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,44), (2,45))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((2,45), (2,46))" id="46" type=".">.</Token>]

token xml [<Token location="((2,46), (2,56))" id="171" type="TOKEN_ID">MiddleName</Token>]

token xml [<Token location="((2,56), (2,57))" id="44" type=",">,</Token>]

token xml [<Token location="((2,57), (2,58))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,58), (2,59))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((2,59), (2,60))" id="46" type=".">.</Token>]

token xml [<Token location="((2,60), (2,68))" id="171" type="TOKEN_ID">LastName</Token>]

token xml [<Token location="((2,68), (2,69))" id="44" type=",">,</Token>]

token xml [<Token location="((2,69), (2,70))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,70), (2,71))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((2,71), (2,72))" id="46" type=".">.</Token>]

token xml [<Token location="((2,72), (2,78))" id="171" type="TOKEN_ID">Suffix</Token>]

token xml [<Token location="((2,78), (2,79))" id="44" type=",">,</Token>]

token xml [<Token location="((2,79), (2,80))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,80), (2,81))" id="171" type="TOKEN_ID">e</Token>]

token xml [<Token location="((2,81), (2,82))" id="46" type=".">.</Token>]

token xml [<Token location="((2,82), (2,87))" id="171" type="TOKEN_ID">Title</Token>]

token xml [<Token location="((2,87), (2,88))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,88), (2,90))" id="203" type="TOKEN_AS">AS</Token>]

token xml [<Token location="((2,90), (2,91))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,91), (2,99))" id="171" type="TOKEN_ID">JobTitle</Token>]

token xml [<Token location="((2,99), (2,100))" id="44" type=",">,</Token>]

token xml [<Token location="((2,100), (2,101))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,101), (2,102))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((2,102), (2,103))" id="46" type=".">.</Token>]

token xml [<Token location="((2,103), (2,108))" id="171" type="TOKEN_ID">Phone</Token>]

token xml [<Token location="((2,108), (2,109))" id="44" type=",">,</Token>]

token xml [<Token location="((2,109), (2,110))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,110), (2,111))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((2,111), (2,112))" id="46" type=".">.</Token>]

token xml [<Token location="((2,112), (2,124))" id="171" type="TOKEN_ID">EmailAddress</Token>]

token xml [<Token location="((2,124), (2,125))" id="44" type=",">,</Token>]

token xml [<Token location="((2,125), (2,126))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,126), (2,127))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((2,127), (2,128))" id="46" type=".">.</Token>]

token xml [<Token location="((2,128), (2,142))" id="171" type="TOKEN_ID">EmailPromotion</Token>]

token xml [<Token location="((2,142), (2,143))" id="44" type=",">,</Token>]

token xml [<Token location="((2,143), (2,144))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,144), (2,145))" id="171" type="TOKEN_ID">a</Token>]

token xml [<Token location="((2,145), (2,146))" id="46" type=".">.</Token>]

token xml [<Token location="((2,146), (2,158))" id="171" type="TOKEN_ID">AddressLine1</Token>]

token xml [<Token location="((2,158), (2,159))" id="44" type=",">,</Token>]

token xml [<Token location="((2,159), (2,160))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,160), (2,161))" id="171" type="TOKEN_ID">a</Token>]

token xml [<Token location="((2,161), (2,162))" id="46" type=".">.</Token>]

token xml [<Token location="((2,162), (2,174))" id="171" type="TOKEN_ID">AddressLine2</Token>]

token xml [<Token location="((2,174), (2,175))" id="44" type=",">,</Token>]

token xml [<Token location="((2,175), (2,176))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,176), (2,177))" id="171" type="TOKEN_ID">a</Token>]

token xml [<Token location="((2,177), (2,178))" id="46" type=".">.</Token>]

token xml [<Token location="((2,178), (2,182))" id="171" type="TOKEN_ID">City</Token>]

token xml [<Token location="((2,182), (2,183))" id="44" type=",">,</Token>]

token xml [<Token location="((2,183), (2,184))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,184), (2,186))" id="171" type="TOKEN_ID">sp</Token>]

token xml [<Token location="((2,186), (2,187))" id="46" type=".">.</Token>]

token xml [<Token location="((2,187), (2,191))" id="171" type="TOKEN_ID">Name</Token>]

token xml [<Token location="((2,191), (2,192))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,192), (2,194))" id="203" type="TOKEN_AS">AS</Token>]

token xml [<Token location="((2,194), (2,195))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,195), (2,212))" id="171" type="TOKEN_ID">StateProvinceName</Token>]

token xml [<Token location="((2,212), (2,213))" id="44" type=",">,</Token>]

token xml [<Token location="((2,213), (2,214))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,214), (2,215))" id="171" type="TOKEN_ID">a</Token>]

token xml [<Token location="((2,215), (2,216))" id="46" type=".">.</Token>]

token xml [<Token location="((2,216), (2,226))" id="171" type="TOKEN_ID">PostalCode</Token>]

token xml [<Token location="((2,226), (2,227))" id="44" type=",">,</Token>]

token xml [<Token location="((2,227), (2,228))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,228), (2,230))" id="171" type="TOKEN_ID">cr</Token>]

token xml [<Token location="((2,230), (2,231))" id="46" type=".">.</Token>]

token xml [<Token location="((2,231), (2,235))" id="171" type="TOKEN_ID">Name</Token>]

token xml [<Token location="((2,235), (2,236))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,236), (2,238))" id="203" type="TOKEN_AS">AS</Token>]

token xml [<Token location="((2,238), (2,239))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,239), (2,256))" id="171" type="TOKEN_ID">CountryRegionName</Token>]

token xml [<Token location="((2,256), (2,257))" id="44" type=",">,</Token>]

token xml [<Token location="((2,257), (2,258))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((2,258), (2,259))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((2,259), (2,260))" id="46" type=".">.</Token>]

token xml [<Token location="((2,260), (2,281))" id="171" type="TOKEN_ID">AdditionalContactInfo</Token>]

token xml [<Token location="((2,281), (3,1))" id="498" type="LEX_WHITE"> \r</Token>]

token xml [<Token location="((3,1), (3,5))" id="242" type="TOKEN_FROM">FROM</Token>]

token xml [<Token location="((3,5), (3,6))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((3,6), (3,20))" id="171" type="TOKEN_ID">HumanResources</Token>]

token xml [<Token location="((3,20), (3,21))" id="46" type=".">.</Token>]

token xml [<Token location="((3,21), (3,29))" id="171" type="TOKEN_ID">Employee</Token>]

token xml [<Token location="((3,29), (3,30))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((3,30), (3,32))" id="203" type="TOKEN_AS">AS</Token>]

token xml [<Token location="((3,32), (3,33))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((3,33), (3,34))" id="171" type="TOKEN_ID">e</Token>]

token xml [<Token location="((3,34), (3,35))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((3,35), (3,40))" id="150" type="TOKEN_INNER">INNER</Token>]

token xml [<Token location="((3,40), (3,41))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((3,41), (3,45))" id="155" type="TOKEN_JOIN">JOIN</Token>]

token xml [<Token location="((3,45), (4,1))" id="498" type="LEX_WHITE"> \r</Token>]

token xml [<Token location="((4,1), (4,7))" id="171" type="TOKEN_ID">Person</Token>]

token xml [<Token location="((4,7), (4,8))" id="46" type=".">.</Token>]

token xml [<Token location="((4,8), (4,15))" id="171" type="TOKEN_ID">Contact</Token>]

token xml [<Token location="((4,15), (4,16))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((4,16), (4,18))" id="203" type="TOKEN_AS">AS</Token>]

token xml [<Token location="((4,18), (4,19))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((4,19), (4,20))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((4,20), (4,21))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((4,21), (4,23))" id="269" type="TOKEN_ON">ON</Token>]

token xml [<Token location="((4,23), (4,24))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((4,24), (4,25))" id="171" type="TOKEN_ID">c</Token>]

token xml [<Token location="((4,25), (4,26))" id="46" type=".">.</Token>]

token xml [<Token location="((4,26), (4,35))" id="171" type="TOKEN_ID">ContactID</Token>]

token xml [<Token location="((4,35), (4,36))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((4,36), (4,37))" id="61" type="=">=</Token>]

token xml [<Token location="((4,37), (4,38))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((4,38), (4,39))" id="171" type="TOKEN_ID">e</Token>]

token xml [<Token location="((4,39), (4,40))" id="46" type=".">.</Token>]

token xml [<Token location="((4,40), (4,49))" id="171" type="TOKEN_ID">ContactID</Token>]

token xml [<Token location="((4,49), (4,50))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((4,50), (4,55))" id="150" type="TOKEN_INNER">INNER</Token>]

token xml [<Token location="((4,55), (4,56))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((4,56), (4,60))" id="155" type="TOKEN_JOIN">JOIN</Token>]

token xml [<Token location="((4,60), (5,1))" id="498" type="LEX_WHITE"> \r</Token>]

token xml [<Token location="((5,1), (5,15))" id="171" type="TOKEN_ID">HumanResources</Token>]

token xml [<Token location="((5,15), (5,16))" id="46" type=".">.</Token>]

token xml [<Token location="((5,16), (5,31))" id="171" type="TOKEN_ID">EmployeeAddress</Token>]

token xml [<Token location="((5,31), (5,32))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((5,32), (5,34))" id="203" type="TOKEN_AS">AS</Token>]

token xml [<Token location="((5,34), (5,35))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((5,35), (5,37))" id="171" type="TOKEN_ID">ea</Token>]

token xml [<Token location="((5,37), (5,38))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((5,38), (5,40))" id="269" type="TOKEN_ON">ON</Token>]

token xml [<Token location="((5,40), (5,41))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((5,41), (5,42))" id="171" type="TOKEN_ID">e</Token>]

token xml [<Token location="((5,42), (5,43))" id="46" type=".">.</Token>]

token xml [<Token location="((5,43), (5,53))" id="171" type="TOKEN_ID">EmployeeID</Token>]

token xml [<Token location="((5,53), (5,54))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((5,54), (5,55))" id="61" type="=">=</Token>]

token xml [<Token location="((5,55), (5,56))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((5,56), (5,58))" id="171" type="TOKEN_ID">ea</Token>]

token xml [<Token location="((5,58), (5,59))" id="46" type=".">.</Token>]

token xml [<Token location="((5,59), (5,69))" id="171" type="TOKEN_ID">EmployeeID</Token>]

token xml [<Token location="((5,69), (5,70))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((5,70), (5,75))" id="150" type="TOKEN_INNER">INNER</Token>]

token xml [<Token location="((5,75), (5,76))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((5,76), (5,80))" id="155" type="TOKEN_JOIN">JOIN</Token>]

token xml [<Token location="((5,80), (6,1))" id="498" type="LEX_WHITE"> \r</Token>]

token xml [<Token location="((6,1), (6,7))" id="171" type="TOKEN_ID">Person</Token>]

token xml [<Token location="((6,7), (6,8))" id="46" type=".">.</Token>]

token xml [<Token location="((6,8), (6,15))" id="171" type="TOKEN_ID">Address</Token>]

token xml [<Token location="((6,15), (6,16))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((6,16), (6,18))" id="203" type="TOKEN_AS">AS</Token>]

token xml [<Token location="((6,18), (6,19))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((6,19), (6,20))" id="171" type="TOKEN_ID">a</Token>]

token xml [<Token location="((6,20), (6,21))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((6,21), (6,23))" id="269" type="TOKEN_ON">ON</Token>]

token xml [<Token location="((6,23), (6,24))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((6,24), (6,26))" id="171" type="TOKEN_ID">ea</Token>]

token xml [<Token location="((6,26), (6,27))" id="46" type=".">.</Token>]

token xml [<Token location="((6,27), (6,36))" id="171" type="TOKEN_ID">AddressID</Token>]

token xml [<Token location="((6,36), (6,37))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((6,37), (6,38))" id="61" type="=">=</Token>]

token xml [<Token location="((6,38), (6,39))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((6,39), (6,40))" id="171" type="TOKEN_ID">a</Token>]

token xml [<Token location="((6,40), (6,41))" id="46" type=".">.</Token>]

token xml [<Token location="((6,41), (6,50))" id="171" type="TOKEN_ID">AddressID</Token>]

token xml [<Token location="((6,50), (6,51))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((6,51), (6,56))" id="150" type="TOKEN_INNER">INNER</Token>]

token xml [<Token location="((6,56), (6,57))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((6,57), (6,61))" id="155" type="TOKEN_JOIN">JOIN</Token>]

token xml [<Token location="((6,61), (7,1))" id="498" type="LEX_WHITE"> \r</Token>]

token xml [<Token location="((7,1), (7,7))" id="171" type="TOKEN_ID">Person</Token>]

token xml [<Token location="((7,7), (7,8))" id="46" type=".">.</Token>]

token xml [<Token location="((7,8), (7,21))" id="171" type="TOKEN_ID">StateProvince</Token>]

token xml [<Token location="((7,21), (7,22))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((7,22), (7,24))" id="203" type="TOKEN_AS">AS</Token>]

token xml [<Token location="((7,24), (7,25))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((7,25), (7,27))" id="171" type="TOKEN_ID">sp</Token>]

token xml [<Token location="((7,27), (7,28))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((7,28), (7,30))" id="269" type="TOKEN_ON">ON</Token>]

token xml [<Token location="((7,30), (7,31))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((7,31), (7,33))" id="171" type="TOKEN_ID">sp</Token>]

token xml [<Token location="((7,33), (7,34))" id="46" type=".">.</Token>]

token xml [<Token location="((7,34), (7,49))" id="171" type="TOKEN_ID">StateProvinceID</Token>]

token xml [<Token location="((7,49), (7,50))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((7,50), (7,51))" id="61" type="=">=</Token>]

token xml [<Token location="((7,51), (7,52))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((7,52), (7,53))" id="171" type="TOKEN_ID">a</Token>]

token xml [<Token location="((7,53), (7,54))" id="46" type=".">.</Token>]

token xml [<Token location="((7,54), (7,69))" id="171" type="TOKEN_ID">StateProvinceID</Token>]

token xml [<Token location="((7,69), (7,70))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((7,70), (7,75))" id="150" type="TOKEN_INNER">INNER</Token>]

token xml [<Token location="((7,75), (7,76))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((7,76), (7,80))" id="155" type="TOKEN_JOIN">JOIN</Token>]

token xml [<Token location="((7,80), (8,1))" id="498" type="LEX_WHITE"> \r</Token>]

token xml [<Token location="((8,1), (8,7))" id="171" type="TOKEN_ID">Person</Token>]

token xml [<Token location="((8,7), (8,8))" id="46" type=".">.</Token>]

token xml [<Token location="((8,8), (8,21))" id="171" type="TOKEN_ID">CountryRegion</Token>]

token xml [<Token location="((8,21), (8,22))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((8,22), (8,24))" id="203" type="TOKEN_AS">AS</Token>]

token xml [<Token location="((8,24), (8,25))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((8,25), (8,27))" id="171" type="TOKEN_ID">cr</Token>]

token xml [<Token location="((8,27), (8,28))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((8,28), (8,30))" id="269" type="TOKEN_ON">ON</Token>]

token xml [<Token location="((8,30), (8,31))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((8,31), (8,33))" id="171" type="TOKEN_ID">cr</Token>]

token xml [<Token location="((8,33), (8,34))" id="46" type=".">.</Token>]

token xml [<Token location="((8,34), (8,51))" id="171" type="TOKEN_ID">CountryRegionCode</Token>]

token xml [<Token location="((8,51), (8,52))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((8,52), (8,53))" id="61" type="=">=</Token>]

token xml [<Token location="((8,53), (8,54))" id="498" type="LEX_WHITE"> </Token>]

token xml [<Token location="((8,54), (8,56))" id="171" type="TOKEN_ID">sp</Token>]

token xml [<Token location="((8,56), (8,57))" id="46" type=".">.</Token>]

token xml [<Token location="((8,57), (8,74))" id="171" type="TOKEN_ID">CountryRegionCode</Token>]

token xml [<Token location="((8,74), (9,1))" id="498" type="LEX_WHITE"> \r</Token>]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!