Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Replacing some xml

Author  Topic 

dexter.knudson
Constraint Violating Yak Guru

260 Posts
Posted - 2009-02-15 : 17:48:28
I would like to replace all occurrences of
"<DefaultValues> ....</Values>" in the xml like the text below with an empty string, except where it contains nil, %, All or Printable.

For example "<DefaultValues>
<Value>ReportDefaultUser</Value>
</DefaultValues>
<Values>
<Value>ReportDefaultUser</Value>
</Values>"
becomes ""
--------------------------
<Parameters>
<UserProfileState>0</UserProfileState>
<Parameter>
<Name>user_name</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>user name</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>ReportDefaultUser</Value>
</DefaultValues>
<Values>
<Value>ReportDefaultUser</Value>
</Values>
</Parameter>
<Parameter>
<Name>job_no</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>job no</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>221321</Value>
</DefaultValues>
<Values>
<Value>221321</Value>
</Values>
</Parameter>
<Parameter>
<Name>package_id</Name>
<Type>String</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>package id</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value nil="True" />
</DefaultValues>
<Values>
<Value nil="True" />
</Values>
</Parameter>
<Parameter>
<Name>project_component_id</Name>
<Type>String</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>project component id</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value nil="True" />
</DefaultValues>
<Values>
<Value nil="True" />
</Values>
</Parameter>
<Parameter>
<Name>date_supplied_from</Name>
<Type>DateTime</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>date supplied from</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>10/01/2005 00:00:00</Value>
</DefaultValues>
<Values>
<Value>10/01/2005 00:00:00</Value>
</Values>
</Parameter>
<Parameter>
<Name>date_supplied_to</Name>
<Type>DateTime</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>date supplied to</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>10/31/2005 00:00:00</Value>
</DefaultValues>
<Values>
<Value>10/31/2005 00:00:00</Value>
</Values>
</Parameter>
<Parameter>
<Name>batch_plant_id</Name>
<Type>Integer</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>batch plant id</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value nil="True" />
</DefaultValues>
<Values>
<Value nil="True" />
</Values>
</Parameter>
<Parameter>
<Name>supplier_id</Name>
<Type>Integer</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>supplier id</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>141</Value>
</DefaultValues>
<Values>
<Value>141</Value>
</Values>
</Parameter>
<Parameter>
<Name>supplier_mix_code</Name>
<Type>String</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>supplier mix code</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>ZN31RTAA</Value>
</DefaultValues>
<Values>
<Value>ZN31RTAA</Value>
</Values>
</Parameter>
<Parameter>
<Name>calc_method</Name>
<Type>Integer</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>calc method</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>1</Value>
</DefaultValues>
<Values>
<Value>1</Value>
</Values>
</Parameter>
</Parameters>
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-02-15 : 21:35:45
have a look at syntax of modify() function in books online.
Go to Top of Page

dexter.knudson
Constraint Violating Yak Guru

260 Posts
Posted - 2009-02-15 : 22:05:07
Hi Visahk,
Thanks for your reply. Unfortunately, I don't have the time to learn how to manage xml in SQL Server. I was hoping that someone may have lots of experience with this & be able to give more detail.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-02-16 : 00:41:13
try this:-

declare @var xml,@n int
set @var='<Parameters>
<UserProfileState>0</UserProfileState>
<Parameter>
<Name>user_name</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>user name</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>ReportDefaultUser</Value>
</DefaultValues>
<Values>
<Value>ReportDefaultUser</Value>
</Values>
</Parameter>
<Parameter>
<Name>job_no</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>job no</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>221321</Value>
</DefaultValues>
<Values>
<Value>221321</Value>
</Values>
</Parameter>
<Parameter>
<Name>package_id</Name>
<Type>String</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>package id</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value nil="True" />
</DefaultValues>
<Values>
<Value nil="True" />
</Values>
</Parameter>
<Parameter>
<Name>project_component_id</Name>
<Type>String</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>project component id</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value nil="True" />
</DefaultValues>
<Values>
<Value nil="True" />
</Values>
</Parameter>
<Parameter>
<Name>date_supplied_from</Name>
<Type>DateTime</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>date supplied from</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>10/01/2005 00:00:00</Value>
</DefaultValues>
<Values>
<Value>10/01/2005 00:00:00</Value>
</Values>
</Parameter>
<Parameter>
<Name>date_supplied_to</Name>
<Type>DateTime</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>date supplied to</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>10/31/2005 00:00:00</Value>
</DefaultValues>
<Values>
<Value>10/31/2005 00:00:00</Value>
</Values>
</Parameter>
<Parameter>
<Name>batch_plant_id</Name>
<Type>Integer</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>batch plant id</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value nil="True" />
</DefaultValues>
<Values>
<Value nil="True" />
</Values>
</Parameter>
<Parameter>
<Name>supplier_id</Name>
<Type>Integer</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>supplier id</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>141</Value>
</DefaultValues>
<Values>
<Value>141</Value>
</Values>
</Parameter>
<Parameter>
<Name>supplier_mix_code</Name>
<Type>String</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>supplier mix code</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>ZN31RTAA</Value>
</DefaultValues>
<Values>
<Value>ZN31RTAA</Value>
</Values>
</Parameter>
<Parameter>
<Name>calc_method</Name>
<Type>Integer</Type>
<Nullable>True</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>True</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>calc method</Prompt>
<DynamicPrompt>False</DynamicPrompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>1</Value>
</DefaultValues>
<Values>
<Value>1</Value>
</Values>
</Parameter>
</Parameters>'
SELECT @var

SET @var.modify('delete  /Parameters/Parameter/DefaultValues/* ')
 
SELECT @var
Go to Top of Page

dexter.knudson
Constraint Violating Yak Guru

260 Posts
Posted - 2009-02-16 : 16:41:20
Hi Visahk,
Thanks again. That looks like a good step in the right direction. Unfortunately I am receiving an error when I open the report after updating the column using the above code: "
The input XML does not conform to the schema. XML grammar is described in the API documentation. For XML in reports, refer to Report Definition Language syntax." I think the "<DefaultValues />" & "<Values />" should also be deleted- ie "<DefaultValues /><Values />" becomes "".
The XML schema needs to conform to: http://msdn.microsoft.com/en-us/library/ms153649(SQL.90).aspx
Also I will need the conditional logic (Set to "", except where the string contains nil, %, All or Printable)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site