May 24, 2013 at 12:45 pm
Hello, I posted something similar on another site but haven't work this out yet so I figure I give it a try here.
I'm trying to create a SQL query that selects pieces of a field, combines them and displays them in a new column for each row. Here is a shorten example of what is in one unedited field:
<Name>Example1</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<Prompt>Start Date (DD-MMM-YYYY)</Prompt>
<PromptUser>True</PromptUser> </Parameter>
<Parameter>
<Name>Example2</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<Prompt>Case (Enter Case Number, % for all, OR %AL% for Alberta)</Prompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>%al%</Value>
</DefaultValues>
<Values>
<Value>%al%</Value>
</Values> </Parameter>
<Parameter>
When the code is done the new field in the column using the above data should look like this
Name: Example1
Prompt: Start Date (DD-MMM-YYYY)
Name: Example2
Prompt: Case (Enter Case Number, % for all, OR %AL% for Alberta)
Value: %al%
In other words I want to remove the names, prompts, and value contained in the field for each record. I have tried to do this in different ways, by using substrings and charindex which fails due to the field having more than one character as shown above (2 <name>). And I have tried though nexted replacing but that failed as well since I don't know what could be between each set of tags (<><>).
if I can use REPLACE to replace the unknown length string between or along with the two known tags for example replacing ### between <Type>###</Type> where ### represents any number of unkown characters then i can solve this problem with the jumbo replace statements.
May 24, 2013 at 12:58 pm
By the way here is what my current jumbo replace statment output looks like for one field out the number of rows if we used the above smple for the orginal field
Name: Example1
String
False
False
Prompt: Start Date (DD-MMM-YYYY)
Name: Example2
String
False
False
Prompt: Case (Enter Case Number, % for all, OR %AL% for Alberta)
True
Value: %al%
%al%
Again I only need the names, prompts, and values contain in each field. So I would need to remove what I have bolded each time and these bolded things could be any character or number of characters the select query creates a new row along with what the Jumbo nested replace statments removes.
I would post the Jumbo Replace statment but the site won't let me lol.
Thanks for the help
May 24, 2013 at 1:03 pm
This looks vaguely like XML? Is the actual data real xml or this type of partial non-parsable xml-ish stuff?
If it is actual XML you can use XQuery.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply