locked
Xp_cmdShell not working with XMLNAMESPACES for xml file export RRS feed

  • Question

  • User1624600859 posted

    Hi all,

    I have a for xml query that is generating an xml following is my code successfully below is the code for the same

    DECLARE @FILEPATH1 VARCHAR(100)
    DECLARE @Query1 VARCHAR(100)
    declare @bcp1 varchar(500)
    --declare @xml XML  --not using
    SET @FILEPATH1='D:\test.xml'
     
    SET @Query1 ='"  SELECT id, taskname FROM [mydb].dbo.tasks where id=1 FOR XML PATH(''Document''), ROOT(''ns0'')"'
    SET @bcp1=' bcp ' + @Query1 + ' queryout ' + @FILEPATH1 + ' -c -T'
    EXEC xp_cmdshell @bcp1

    but if I replace my query with the below one it fails

    WITH XMLNAMESPACES ('http://abc.com' as ns0)  
    SELECT id,
           'taskname' as [taskname_display/task],
           'te' as [taskname_display/task/@code]
       
    FROM [mydb].dbo.tasks where id=1 FOR XML PATH(''), ROOT('ns0:document')
     
     
     
    I need xml file to be saved in the mentioned filepath with the below output. How can I achieve this. As xml query result it is ok and achievable, but using with xp_cmdshell it fails
     
     
    <?xml version="1.0" encoding="UTF-8"?>
    <ns0:document xmlns:ns0="http://abc.com">
    <id>1</id>
    <taskname_display>
            <task  code="te">1</task >
    </taskname_display>
    </ns0:document>
     

    Friday, January 4, 2019 10:32 AM

All replies

  • User753101303 posted

    Hi

    t fails

    It's best to always start from an error message. You are 100% sure it works in SSMS ? bcp doesn't show an error message that could be used as a starting point ?

    Friday, January 4, 2019 1:09 PM
  • User1624600859 posted

    Hi

    Well I was able to generate xml based on my choice of node and attribute in the xml. I have two issues now one with XMLNAMESPACES and the other is that encoding attribute is not coming (<?xml version="1.0" encoding="UTF-8"?>).. The bold part is not in the output 

    Working Code:
    DECLARE @FILEPATH1 VARCHAR(100)
    DECLARE @Query1 VARCHAR(500)
    declare @bcp1 varchar(500)

    SET @FILEPATH1='D:\test.xml'

    SET @Query1 ='" SELECT id, name as [rec/recname] FROM [mydb].dbo.tasks where id=1 FOR XML PATH(''''), ROOT(''document'')"'

    SET @bcp1=' bcp ' + @Query1 + ' queryout ' + @FILEPATH1 + ' -c -T -x'
    EXEC xp_cmdshell @bcp1

    OUTPUT

    <div><?xml version="1.0"?>  -- Encoding attribute missing in the generated xml file at location D:\test.xml</div> <div><document></div> <div><id>1</id></div> <div><rec></div> <div>  <recname>test</recname></div> <div></rec></div> <div></document></div> <div></div> <div>Code with issue</div> <div>

    DECLARE @FILEPATH1 VARCHAR(100)
    DECLARE @Query1 VARCHAR(500)
    declare @bcp1 varchar(500)

    SET @FILEPATH1='D:\test.xml'

    SET @Query1 ='";WITH XMLNAMESPACES (DEFAULT 'http://www.abc.com/Documents' as ''ns0'') SELECT id, name as [rec/recname] FROM [mydb].dbo.tasks where id=1 FOR XML PATH(''''), ROOT(''ns0:document'')"'

    SET @bcp1=' bcp ' + @Query1 + ' queryout ' + @FILEPATH1 + ' -c -T -x'
    EXEC xp_cmdshell @bcp1

    ERROR - Incorrect syntax near '/'.

    Required OUTPUT

    <div><?xml version="1.0" encoding="UTF-8"?></div> <div><ns0:document xmlns:ns0="http://www.abc.com/Documents"></div> <div><id>1</id></div> <div><rec></div> <div>  <recname>test</recname></div> <div></rec></div> <div></document></div> </div>

    Friday, January 4, 2019 3:55 PM
  • User753101303 posted

    What if you try :
    DEFAULT ''http://www.abc.com/Documents'' (ie using two single quotes here as well)

    I thought it was failing on xp_cmdshell but apparently this is a SQL Server syntax issue (the full error message should clearly show it happens on the SQL Server side rather than on the BCP side). Does it work ?

    Then I'm not sure you can add this prefix. Your best bet would be to append it but I would check first it is REALLY needed. AFAIK if not present it is just an XML 1.0 document with a defautl encoding which should be handled fine by most XML processors. I've seen few times people having to tweak the XML document because the other side was processing badly the XML file (even one time someone who required to have a CR LF after each closing tag).

    Not directly related but I prefer to enable and use xp_cmdshell unless it is really required. If triggered from a client app, I would just return an XML document and would let the client app to deal with the XML response. 

    Friday, January 4, 2019 4:42 PM
  • User1624600859 posted

    Well requirement is to get the xml from sql server and place the file on a location, that is why  going through this approach. The way doing it from client, yeah , agree that it is a good one even I was wondering the same but have to cope up with the requirement. Further regarding the encoding parameter that is missing in the ouput as per you if can be handled easily then I am ok with what I am doing currently. Rest, lets see what comes from requirement sender :)

    Saturday, January 5, 2019 4:43 AM
  • User1624600859 posted

    Well got it. Thanks PatriceSc

    replace this 

    SET @Query1 ='";WITH XMLNAMESPACES (DEFAULT 'http://www.abc.com/Documents' as ''ns0'') SELECT id, name as [rec/recname] FROM [mydb].dbo.tasks where id=1 FOR XML PATH(''''), ROOT(''ns0:document'')"'

    with

    SET @Query1 ='"WITH XMLNAMESPACES (''http://www.abc.com/Documents'' as ns0) SELECT id, name as [rec/recname] FROM [mydb].dbo.tasks where id=1 FOR XML PATH(''''), ROOT(''ns0:document'')"'

    Saturday, January 5, 2019 5:24 AM