none
remove xmlns empty atribute

    Question

  • I am writing this query:

    ECLARE @FromRole XML
    declare @ComputerBuildReport xml
    
    SELECT @FromRole =
    (   
        SELECT '1234' as 'Role_ID'
      for xml PATH('FromRole')
    )
    
    
    SELECT @ComputerBuildReport=
    (
      SELECT
      (SELECT 'POSI' as 'GlobalBusinessIdentifier'
       FOR XML PATH('shipFrom'),type)
      FOR XML PATH('ComputerBuildReport'), TYPE
    )
    
    ;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi, DEFAULT 'http://www.kene.com/xmlschema/ComputerBuildReport')
    SELECT @FromRole, @ComputerBuildReport
     
    for XML PATH('PipComputerBuildReport'), TYPE
    

    The result is:

    <PipComputerBuildReport xmlns="http://www.kene.com/B2B/xmlschema/ComputerBuildReport" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <FromRole xmlns="">
        <Role_ID>1234</Role_ID>
      </FromRole>
      <ComputerBuildReport xmlns="">
        <shipFrom>
          <GlobalBusinessIdentifier>POSI</GlobalBusinessIdentifier>
        </shipFrom>
      </ComputerBuildReport>
    </PipComputerBuildReport>

    But I am expecting this as result. 

    <PipComputerBuildReport xmlns="http://www.kene.com/B2B/xmlschema/ComputerBuildReport" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <FromRole>
        <Role_ID>1234</Role_ID>
      </FromRole>
      <ComputerBuildReport>
        <shipFrom>
          <GlobalBusinessIdentifier>POSI</GlobalBusinessIdentifier>
        </shipFrom>
      </ComputerBuildReport>
    </PipComputerBuildReport>

    How can I remove attribute xmlns=""?

    thanks!

    • Déplacé Naomi N mardi 12 février 2013 20:25 Better answer can be here
    mardi 12 février 2013 19:18

Réponses

  • Namespaces can be difficult to control in SQL Server, especially with nested queries.  There are a couple of ways to approach this.  One might be to use hacky string manipulation, which I try and stay away from, ie create the XML without any namespaces, hack one in with REPLACE.

    The other methods are to take control of the XML by constructing it completely, or use FOR XML EXPLICIT for much tighter control, eg

    -- Take control of the XML
    DECLARE @Role_ID INT = 1234
    DECLARE @GlobalBusinessIdentifier VARCHAR(20) = 'POSI'
    
    DECLARE @xml XML = ''
    
    SELECT @xml.query('<PipComputerBuildReport xmlns="http://www.kene.com/B2B/xmlschema/ComputerBuildReport" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    	<FromRole>
    		<Role_ID>{sql:variable("@Role_ID")}</Role_ID>
    	</FromRole>
    	<ComputerBuildReport>
    		<shipFrom>
    			<GlobalBusinessIdentifier>{sql:variable("@GlobalBusinessIdentifier")}</GlobalBusinessIdentifier>
    		</shipFrom>
    	</ComputerBuildReport>
    </PipComputerBuildReport>')
    
    
    
    -- FOR XML EXPLICIT option
    SELECT
    	1 AS TAG,
    	NULL AS Parent,
    	NULL AS [PipComputerBuildReport!1!],
    	'http://www.kene.com/B2B/xmlschema/ComputerBuildReport' AS [PipComputerBuildReport!1!xmlns],
    	'http://www.w3.org/2001/XMLSchema-instance' AS [PipComputerBuildReport!1!xmlns:xsi],
    	NULL AS [FromRole!2!Role_ID!Element],
    	NULL AS [ComputerBuildReport!3!shipFrom!Element],
    	NULL AS [shipFrom!4!GlobalBusinessIdentifier!Element]
    UNION ALL
    SELECT
    	2 AS TAG,
    	1 AS Parent,
    	NULL,
    	NULL,
    	NULL,
    	'1234',
    	NULL,
    	NULL
    UNION ALL
    SELECT
    	3 AS TAG,
    	1 AS Parent,
    	NULL,
    	NULL,
    	NULL,
    	NULL,
    	NULL,
    	NULL
    UNION ALL
    SELECT
    	4 AS TAG,
    	3 AS Parent,
    	NULL,
    	NULL,
    	NULL,
    	NULL,
    	NULL,
    	'POSI'
    
    FOR XML EXPLICIT

    • Marqué comme réponse Juliano_Pessoa mercredi 13 février 2013 13:13
    mardi 12 février 2013 22:02
    Auteur de réponse