asked
Retrieve text from XML in SQL
I have the following code in SQL: declare @xfile table (xfile xml) insert @xfile values (' free text data line 1 free text data line 2 ... free text data line 3 free text data line 4 ... free text data line 5 free text data line 6 ... ') SELECT T2.Loc.query('.') FROM @xfile CROSS APPLY xfile.nodes('/root/setup') as T2(Loc) This returns the following data free text data line 1 free text data line 2 ... free text data line 3 free text data line 4 ... free text data line 5 free text data line 6 ... What I would like is a query that returns two columns: the value of the program attribute, and the free text part of the setup section. For example: Program setup prog1 free text data line 1 free text data line 2 ... prog2 free text data line 3 free text data line 4 ... prog3 free text data line 5 free text data line 6 ... Does anyone know what query I can use to get this result? Thanks in advance.
This might be what you're looking for: select X.SETUP.value('(@program)[1]', 'varchar(20)') as Program, X.SETUP.value('.', 'varchar(max)') as [FreeText] from @xfile CROSS APPLY xfile.nodes('/root/setup') as X(SETUP) That returns the following: +---------+---------------------------------------------------+ | Program | FreeText | +---------+---------------------------------------------------+ | prog1 | free text data line 1 free text data line 2 ... | | prog2 | free text data line 3 free text data line 4 ... | | prog3 | free text data line 5 free text data line 6 ... | +---------+---------------------------------------------------+ You won't see the line breaks in SSMS (hence why FreeText gets smashed together in the output results), but you do get the two columns you want.
9 People are following this question.
