Open XML with Dynamic Schema RRS feed

  • Question

  • So this is my code snippet. all this doing is that it's calling an api to call a web service and get XML data. And then because I want to use this stored procedure dynamically for other XML files I have to create the schema of each table dynamically. [dbo].[Dynamic_Table_Schema_Builder] procedure gives me the schema of the table. But for some reason I can't execute my dynamic sql. It'd be great if someone could help with this. Thanks

    DECLARE @result XML;
    EXEC DATAMART.[dbo].[Fetch_XML_API] @url, @username,@password, @result OUTPUT
    DECLARE @tableName NVARCHAR(100);
    SET @tableName = 'SRN_Z_ClientList'
    print @tableName

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @result
    EXEC [dbo].[Dynamic_Table_Schema_Builder] @tableName, @Schema OUTPUT

    SET @SQL = N'SELECT * FROM OPENXML(' + @hDoc ', ' + @tag + ', 2) WITH (' + @Schema;

    just as a side not the problem is not with @tag because this variable is declared and set somewhere else and it is not included in this code snippet.

    And this is the error I'm getting

    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
    Msg 137, Level 15, State 2, Line 1
    Must declare the scalar variable "@hDoc".

    Friday, June 14, 2019 2:44 PM

All replies

  • Hi nican_af1,

    Please try the following:

    SET @SQL = N'SELECT * FROM OPENXML(' + @hDoc + ', ''' + @tag + ''', 2) WITH (' + @Schema + ')';

    Just as a comment, it is better to use XQuery APIs instead of OPENXML() while handling XML in SQL Server.

    OPENXML() is circa SQL Server 2000 and currently kept just for backward compatibility. 

    Friday, June 14, 2019 2:59 PM
  • SET @SQL = N'SELECT * FROM OPENXML(' + @hDoc + ', ''' + @tag + ''', 2) WITH (' + @Schema + ')';

    But that will not work out, but will die with a conversion error.

    Here is a better way:

    SET N'SELECT * FROM OPENXML(@hDoc, @tag, 2) WITH (' + @Schema + ')'
    EXEC sp_executesql @SQL, N'@hDoc int, @tag varchar(100)', @hDoc, @tag

    Don't interpolate into an SQL string when you can pass them as parameters.

    Erland Sommarskog, SQL Server MVP,

    Friday, June 14, 2019 9:13 PM