Generate the text report and create a folder based on month and save the file with current date using SQL Script

 We have a requirement that we need to extract the data from the SQL server table and save that text file with current date and store that file in the current month folder and if the folder is not available in this case we need to create current month folder automatically using SQL script and store that file in this folder.

Here we are using the “AdventureWorksDW2017” database.

Below is the SQL script to fetch the data from the table.  

Below is the script to extract the data from the table and create the text file and folder.

DECLARE @Folder_Path           VARCHAR(80),         @Folder_Base_Name      VARCHAR(100),         @Folder_Name           VARCHAR(500),         @Sql_File_Path         VARCHAR(4000),         @Execution_Result_Path VARCHAR(4000),         @Server_Name           VARCHAR(20),         @sql_String            VARCHAR(4000),         @Database_Name         VARCHAR(100),         @OLE_Folder_ID         INT,         @OLE_Source            VARCHAR(255),         @OLE_Des               VARCHAR(255),         @init                  INT,         @OLE_File_Sytem_Object INT;  SET @Folder_Path='J:\Product'; SET @Folder_Base_Name ='Product'; SET @Folder_Name = @Folder_Path + '\' + @Folder_Base_Name + '_'                    + CONVERT(VARCHAR(6), Getdate(), 112); SET @Sql_File_Path = 'J:\code\DimProduct.sql'; SET @Execution_Result_Path= @Folder_Name + '\' + 'DimProduct_resultset_'                             + CONVERT(VARCHAR(8), Getdate(), 112) + '.txt'; SET @Server_Name ='BAGESH\BAGESHDB'; SET @Database_Name ='AdventureWorksDW2017';  -- it will fail if OLE automation not enabled EXEC @init=Sp_oacreate   'Scripting.FileSystemObject',   @OLE_File_Sytem_Object out  IF @init <> 0   BEGIN       EXEC Sp_oageterrorinfo         @OLE_File_Sytem_Object        RETURN   END -- check if folder exists EXEC @init=Sp_oamethod   @OLE_File_Sytem_Object,   'FolderExists',   @OLE_Folder_ID out,   @Folder_Name --if @OLE_Folder_ID =1 it means folder is exist if @OLE_Folder_ID=0 folder is not exist -- if folder doesnt exist, create it IF @OLE_Folder_ID = 0   BEGIN       EXEC @init=Sp_oamethod         @OLE_File_Sytem_Object,         'CreateFolder',         @OLE_Folder_ID out,         @Folder_Name   END -- in case of error, raise it  IF @init <> 0   BEGIN       EXEC Sp_oageterrorinfo         @OLE_File_Sytem_Object,         @OLE_Source out,         @OLE_Des out        SELECT @OLE_Des = 'Could not create folder: ' + @OLE_Des        RAISERROR (@OLE_Des,16,1)   END  EXECUTE @init = Sp_oadestroy   @OLE_File_Sytem_Object SET @sql_String = 'SQLCMD -E -S ' + @Server_Name + ' -d '                   + @Database_Name + ' -i ' + @Sql_File_Path + ' -o '                   + @Execution_Result_Path EXEC master..Xp_cmdshell   @sql_String

 We are creating the SQL Server job to run automatically.  

In this folder there are no any folders and files.

Learn here how to create SQL server Jobs in SQL Server Agent:

https://bageshkumarbagi-msbi.blogspot.com/2018/12/creating-new-job-in-sql-server-agent.html

Let’s run this job first time.

  

Job executed successfully.

  

Let’s see the report.

Folder created successfully.

  

In this folder file is created.   

File and file data.

Next day run 

Next month run. 

Now folder created with current year with month.

  

File with file data.

We got the expected result.

Happy Learning!  J

Retrieving XML Data from the database table in sql server

 XML data we are generally storing it into XML data type and when we are using that data into the application we need to shedder the xml data into the rows and columns.

In this demo we will see how to shedder the Xml data into the rows and columns.

Below is the source data.   

With the help of below sql script we will shedder the xml data into the rows and columns.

DECLARE @XMLSource         XML,         @number_of_records INT,         @cnt               INT,         @SearchKeyword     VARCHAR(100); DECLARE @tblxmldata TABLE   (      id            INT IDENTITY(1, 1),      searchkeyword VARCHAR(100),      xmlsource     XML   ) DECLARE @tbldata TABLE   (      searchkeyword VARCHAR(100),      id            VARCHAR(100),      author        VARCHAR(50),      title         VARCHAR(50),      genre         VARCHAR(50),      price         VARCHAR(50),      publish_date  VARCHAR(50),      description   VARCHAR(500)   )   SET @cnt=1;   INSERT INTO @tblxmldata             (searchkeyword,              xmlsource) SELECT searchkeyword,        xmldata FROM   xml_file_details;   SELECT @number_of_records = Count(*) FROM   @tblxmldata;   WHILE @cnt <= @number_of_records   BEGIN       SELECT @SearchKeyword = searchkeyword,              @XMLSource = xmlsource       FROM   @tblxmldata       WHERE  id = @cnt;         INSERT INTO @tbldata                   (searchkeyword,                    id,                    author,                    title,                    genre,                    price,                    publish_date,                    description)       SELECT @SearchKeyword                                     AS SearchKeyword,              src.client.value ('id[1]', 'varchar(100)')         AS ID,              src.client.value('author[1]', 'VARCHAR(50)')       AS author,              src.client.value('title[1]', 'VARCHAR(50)')        AS title,              src.client.value('genre[1]', 'VARCHAR(50)')        AS genre,              src.client.value('price[1]', 'VARCHAR(50)')        AS price,              src.client.value('publish_date[1]', 'VARCHAR(50)') AS publish_date,              src.client.value('description[1]', 'VARCHAR(500)') AS description       FROM   @XMLSource.nodes('catalog/book') AS SRC (client);         SET @cnt=@cnt + 1;   END;   SELECT *  FROM   @tbldata;

 See the result. 

Get the expected result.

Happy Learning! J

Storing the XML data into Rows and Columns in the table

 In the previous post we see how to store the XML file data in the sql server table.

Read here: Storing the XML file into the table

https://bageshkumarbagi-msbi.blogspot.com/2021/07/storing-xml-file-into-table.html

Suppose we want to Shredder the xml file and retrieve the xml file into the rows and column and storing this records into the table.

See the below file.  

It is having the below nodes. These nodes will be the column of the table.

·         id

·         author

·         title

·         genre

·         price

·         publish_date

·         description

Below is the table to store these data.

CREATE TABLE book(                 id varchar(100) NULL,                 author varchar(100) NULL,                 title varchar(100) NULL,                 genre varchar(100) NULL,                 price varchar(100) NULL,                 publish_date varchar(100) NULL,                 description varchar(100) NULL )

 We can Shredder into the below

§  Using sp_xml_preparedocument and sp_xml_removedocument sp

§  Using node function

Using sp_xml_preparedocument and sp_xml_removedocument sp

sp_xml_preparedocument  Reads

The XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes in the XML document: elements, attributes, text, comments, and so on. It returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session.

sp_xml_removedocument :

Removes the internal representation of the XML document specified by the document handle and invalidates the document handle.

Below is the script to Shredder the XML data and load these data into the table.

DECLARE @DocID INT; DECLARE @DocXML VARCHAR(MAX); SELECT @DocXML = CAST(XMLSource AS VARCHAR(MAX)) FROM OPENROWSET(BULK 'H:\SSIS1\Source\XML_Files\Computer.Xml', SINGLE_BLOB) AS X (XMLSource); EXECUTE master.dbo.sp_xml_preparedocument @DocID OUTPUT, @DocXML;  insert into book(                    id                   ,author                   ,title                   ,genre                   ,price                   ,publish_date                   ,description ) SELECT id,author,title,genre,price,publish_date,description FROM OPENXML(@DocID, 'catalog/book', 2) WITH (  id VARCHAR(100) ,author VARCHAR(100) ,title VARCHAR(100) ,genre varchar(100) ,price varchar(100) ,publish_date varchar(100) ,description varchar(100) );  EXECUTE master.dbo.sp_xml_removedocument @DocID;

 Data inserted successfully.  

See the records in the table. 

Using Node function

Below is the script

DECLARE @XMLSource XML; SELECT @XMLSource = CAST(XMLSource AS XML) FROM OPENROWSET(BULK 'H:\SSIS1\Source\XML_Files\Computer.Xml', SINGLE_BLOB) AS X (XMLSource); insert into book(                    id                   ,author                   ,title                   ,genre                   ,price                   ,publish_date                   ,description ) SELECT SRC.Client.value('id[1]', 'varchar(100)') AS ID ,SRC.Client.value('author[1]', 'VARCHAR(100)') AS author ,SRC.Client.value('title[1]', 'VARCHAR(100)') AS title ,SRC.Client.value('genre[1]', 'VARCHAR(100)') AS genre ,SRC.Client.value('price[1]', 'VARCHAR(100)') AS price ,SRC.Client.value('publish_date[1]', 'VARCHAR(100)') AS publish_date ,SRC.Client.value('description[1]', 'VARCHAR(100)') AS description FROM @XMLSource.nodes('catalog/book') AS SRC (Client);

Before running the script  

Running this script.  

See the records into the table