none
sql server issue with xml cast with a namespace

    Question

  • Hi All ,

    why below syntax is giving me error ..

    ; with xmlnamespaces ('http://my/schemas/20120701' as cmp)
    select cast('<cmp:O b="16" />' as xml)

    and  below is working  fine..

    ; with xmlnamespaces ('http://my/schemas/20120701' as cmp)
    select cast('<O b="16" />' as xml)

    Thanks,

    Saurabh


    http://www.linkedin.com/in/sbhadauria http://www.experts-exchange.com/M_6313078.html

    Saturday, August 24, 2013 5:49 AM

Answers

  • Hi Saurabh,

    Second statement is working fine because you have declared namespace as cmp , so need to prefix namespace before each node .

    Try these links for better understanding :

    http://msdn.microsoft.com/en-us/library/aa468565.aspx

    http://technet.microsoft.com/en-us/library/ms177400.aspx

    http://technet.microsoft.com/en-us/library/ms177607.aspx


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Saturday, August 24, 2013 6:55 AM
    Moderator
  • Hi Saurabh,

    Based on the error message, this issue can occur if CAST command doesn’t identify the xml namespace out of it. I suggest using “for xml” command to generate the xml result, you can refer to the following codes and check whether the result is what you want.

    ; with xmlnamespaces ('http://my/schemas/20120701' as cmp)
    select cast('<O b="16" />' as xml) 
    /*
    <O b="16" />
    */
    ; with xmlnamespaces ('http://my/schemas/20120701' as cmp)
    select 16 as 'b'
    for xml raw('cmp:O')
    /*
    <cmp:O xmlns:cmp="http://my/schemas/20120701" b="16"/>
    */


    Allen Li
    TechNet Community Support

    Monday, August 26, 2013 6:19 AM
    Moderator

All replies

  • Hi Saurabh,

    Second statement is working fine because you have declared namespace as cmp , so need to prefix namespace before each node .

    Try these links for better understanding :

    http://msdn.microsoft.com/en-us/library/aa468565.aspx

    http://technet.microsoft.com/en-us/library/ms177400.aspx

    http://technet.microsoft.com/en-us/library/ms177607.aspx


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Saturday, August 24, 2013 6:55 AM
    Moderator
  • Hi Sathya, 

    Thanks for reply ... 

    Actually I have a dynamic string and want to convert it to Xml   and want to add an namespace reference to it.. 

    so any work around for the first query..

    ; with xmlnamespaces ('http://my/schemas/20120701' as cmp)

    select cast('<cmp:O b="16" />' as xml)

    here anyway I want to add a 'cmp' prefix into node 'O' .. 

    Thanks.

    Saurabh


    http://www.linkedin.com/in/sbhadauria http://www.experts-exchange.com/M_6313078.html

    Saturday, August 24, 2013 7:38 AM
  • Hi Saurabh,

    Based on the error message, this issue can occur if CAST command doesn’t identify the xml namespace out of it. I suggest using “for xml” command to generate the xml result, you can refer to the following codes and check whether the result is what you want.

    ; with xmlnamespaces ('http://my/schemas/20120701' as cmp)
    select cast('<O b="16" />' as xml) 
    /*
    <O b="16" />
    */
    ; with xmlnamespaces ('http://my/schemas/20120701' as cmp)
    select 16 as 'b'
    for xml raw('cmp:O')
    /*
    <cmp:O xmlns:cmp="http://my/schemas/20120701" b="16"/>
    */


    Allen Li
    TechNet Community Support

    Monday, August 26, 2013 6:19 AM
    Moderator