Post reply

String Replacement in XML datatype

  • November 25, 2010 at 1:30 am

    #99434

    DECLARE @t table ( xml_text xml)

    INSERT INTO @t

    SELECT '<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/ReportParameters.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields><Prompts><Prompt PromptName="DateRangePrompt"><Param ParamName="@timezoneid" ParamValue="35" /><Param ParamName="@enddate" ParamValue="09/21/2006" /></Prompt><Prompt PromptName="SessionTypePrompt"><Param ParamName="@session_type" ParamValue="1" /></Prompt></Prompts><Filters><Filter FieldStub="9c312bdb-b9ee-4a06-9ab7-84357e22a7e2" OpId="4" FieldValue="1/1/2006" /></Filters></Report>'

    union SELECT '<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/ReportParameters.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields><Prompts><Prompt PromptName="DateRangePrompt"><Param ParamName="@timezoneid" ParamValue="35" /><Param ParamName="@startdate" ParamValue="01/18/2004" /><Param ParamName="@enddate" ParamValue="09/28/2006" /></Prompt><Prompt PromptName="SessionTypePrompt"><Param ParamName="@session_type" ParamValue="1" /></Prompt></Prompts><Filters /></Report>'

    union SELECT '<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/ReportParameters.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields><Prompts><Prompt PromptName="DateRangePrompt"><Param ParamName="@timezoneid" ParamValue="35" /><Param ParamName="@startdate" ParamValue="03/20/2009" /><Param ParamName="@enddate" ParamValue="08/04/2009" /></Prompt><Prompt PromptName="SessionTypePrompt"><Param ParamName="@session_type" ParamValue="2" /></Prompt></Prompts><Filters SearchType="AND" /></Report>'

    select * from @t

    i need to remove

    .xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields>

    from xml_text field.

    below query is not working

    UPDATE @t

    SET xml_text = CAST(REPLACE (CAST(xml_text AS NVARCHAR(MAX)),'.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields>','.xsd">' ) AS XML)

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • November 25, 2010 at 2:51 am

    #1255488

    DECLARE @t table ( xml_text xml)

    INSERT INTO @t

    SELECT '<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/ReportParameters.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields><Prompts><Prompt PromptName="DateRangePrompt"><Param ParamName="@timezoneid" ParamValue="35" /><Param ParamName="@enddate" ParamValue="09/21/2006" /></Prompt><Prompt PromptName="SessionTypePrompt"><Param ParamName="@session_type" ParamValue="1" /></Prompt></Prompts><Filters><Filter FieldStub="9c312bdb-b9ee-4a06-9ab7-84357e22a7e2" OpId="4" FieldValue="1/1/2006" /></Filters></Report>'

    union SELECT '<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/ReportParameters.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields><Prompts><Prompt PromptName="DateRangePrompt"><Param ParamName="@timezoneid" ParamValue="35" /><Param ParamName="@startdate" ParamValue="01/18/2004" /><Param ParamName="@enddate" ParamValue="09/28/2006" /></Prompt><Prompt PromptName="SessionTypePrompt"><Param ParamName="@session_type" ParamValue="1" /></Prompt></Prompts><Filters /></Report>'

    union SELECT '<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/ReportParameters.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields><Prompts><Prompt PromptName="DateRangePrompt"><Param ParamName="@timezoneid" ParamValue="35" /><Param ParamName="@startdate" ParamValue="03/20/2009" /><Param ParamName="@enddate" ParamValue="08/04/2009" /></Prompt><Prompt PromptName="SessionTypePrompt"><Param ParamName="@session_type" ParamValue="2" /></Prompt></Prompts><Filters SearchType="AND" /></Report>'

    ;with xmlnamespaces(

    'http://tempuri.org/ReportParameters.xsd' as x

    )

    update @t

    set xml_text.modify('delete //x:Fields')

    select *

    from @t

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • November 25, 2010 at 3:56 am

    #1255503

    thanks but one issue with the results

    <Report xmlns:xsi="

    got replaced with

    <Report xmlns="

    but i need

    :xsi

    text also.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • November 25, 2010 at 4:00 am

    #1255505

    Sorry - it's because I left out the other namespaces... try this:

    DECLARE @t table ( xml_text xml)

    INSERT INTO @t

    SELECT '<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/ReportParameters.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields><Prompts><Prompt PromptName="DateRangePrompt"><Param ParamName="@timezoneid" ParamValue="35" /><Param ParamName="@enddate" ParamValue="09/21/2006" /></Prompt><Prompt PromptName="SessionTypePrompt"><Param ParamName="@session_type" ParamValue="1" /></Prompt></Prompts><Filters><Filter FieldStub="9c312bdb-b9ee-4a06-9ab7-84357e22a7e2" OpId="4" FieldValue="1/1/2006" /></Filters></Report>'

    union SELECT '<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/ReportParameters.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields><Prompts><Prompt PromptName="DateRangePrompt"><Param ParamName="@timezoneid" ParamValue="35" /><Param ParamName="@startdate" ParamValue="01/18/2004" /><Param ParamName="@enddate" ParamValue="09/28/2006" /></Prompt><Prompt PromptName="SessionTypePrompt"><Param ParamName="@session_type" ParamValue="1" /></Prompt></Prompts><Filters /></Report>'

    union SELECT '<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/ReportParameters.xsd"><Fields><Field FieldStub="D51C8428-8436-40B8-A1B0-FE1EC2C24401" FieldName="Product Code" FieldType="512" /></Fields><Prompts><Prompt PromptName="DateRangePrompt"><Param ParamName="@timezoneid" ParamValue="35" /><Param ParamName="@startdate" ParamValue="03/20/2009" /><Param ParamName="@enddate" ParamValue="08/04/2009" /></Prompt><Prompt PromptName="SessionTypePrompt"><Param ParamName="@session_type" ParamValue="2" /></Prompt></Prompts><Filters SearchType="AND" /></Report>'

    ;with xmlnamespaces(

    'http://www.w3.org/2001/XMLSchema-instance' as xsi,

    'http://www.w3.org/2001/XMLSchema' as xsd,

    'http://tempuri.org/ReportParameters.xsd' as x

    )

    update @t

    set xml_text.modify('delete //x:Fields')

    select *

    from @t

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • November 25, 2010 at 4:05 am

    #1255507

    MM. Thanks for quick response.

    Actually

    the output's sequence is not as you mentioned in WITH clause.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • November 25, 2010 at 4:07 am

    #1255509

    You're welcome, but I do not understand your last comment, unless you mean the ordering of the namespaces in the resulting xml is different?

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • November 25, 2010 at 4:10 am

    #1255511

    Forget it ....thanks for your great support

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

    • Viewing 7 posts - 1 through 6 (of 6 total)

    You must be logged in to reply to this topic. Login to reply