XML Parsing View

Question

Good Day

I have a query that views and sorts the contents of my SQL. It's easy if you know what the xml file is but I am trying to create the xml file as a parameter because it's not always the same. How can I add the parameter to the path? I did try but it says it's incorrect.

This is the code in a view that works:

   select
        c3.value('@CtlgID','nvarchar(50)') AS 'ID',
        c4.value('@label','nvarchar(50)') AS 'ID',
        c5.value('@label','nvarchar(50)') AS 'ID'
    from
    (
       select 
          cast(c1 as xml)
       from 
          OPENROWSET (BULK 'C:\ISP\bin\EN\XML\Cataloghi\menuCat_756.xml',SINGLE_BLOB) as T1(c1)

    )as T2(c2)
    cross apply c2.nodes('/node') T3(c3)
    cross apply c2.nodes('/node/node') T4(c4)
    cross apply c2.nodes('/node/node/node') T5(c5)

I am trying to add this to a stored procedure:

PROCEDURE [dbo].[Update_ISP_Child] 
    -- Add the parameters for the stored procedure here
    @p1 nvarchar(50) = 'menuCat_756.xml'
AS
BEGIN

    select
    c3.value('@CtlgID','nvarchar(50)') AS 'ID',
    c4.value('@label','nvarchar(50)') AS 'ID',
    c5.value('@label','nvarchar(50)') AS 'ID'
from
(
   select 
      cast(c1 as xml)
   from 
      OPENROWSET (BULK 'C:\ISP\bin\EN\XML\Cataloghi\' + @p1,SINGLE_BLOB) as T1(c1)

)as T2(c2)
cross apply c2.nodes('/node') T3(c3)
cross apply c2.nodes('/node/node') T4(c4)
cross apply c2.nodes('/node/node/node') T5(c5)

END

When I add my parameter as @p1 it doesn't work.

Thanks.

Ruan

Answer 1

You have to build the whole statement dynamically:

--the file's name
DECLARE @filename VARCHAR(250)='menuCat_756.xml';

--a tolerant staging table to help us get the result of EXEC
DECLARE @staging TABLE(TheXml NVARCHAR(MAX));

--a dynamically created command
DECLARE @cmd NVARCHAR(MAX)=
N'select c1
  from OPENROWSET (BULK ''C:\ISP\bin\EN\XML\Cataloghi\' + @filename + ''',SINGLE_CLOB) as T1(c1)';

--This will shift the "result set" (which is one single XML) into our staging table
INSERT INTO @staging 
EXEC(@cmd);

--Test it
SELECT * FROM @staging

Hint 1: I changed SINLGE_BLOB to SINLGE_CLOB

Hint 2: You must cast the result to XML before you use it as XML.
It is good practice to do casts after the import to avoid errors hardly to find...