Envelop Schemas Walk Through BizTalk

n

An envelope schema is a special type of XML schema. Envelope schemas are used to define the structure of XML envelopes, which are used to wrap one or more XML business documents into a single XML instance message. When you define an XML schema to be an envelope schema, a couple of additional property settings are required, depending on such factors as whether there are more than one root record defined in the envelope schema
Envelope schema can be used with the Xml Disassembler to break large XML messages into Individual Messages.

This example shows how to split a large XML message into parts, so each part can be processed separately.

Image you have following message that contains information about one or more customers; the Customers node can contain any number Customer nodes.

<Customers>
<Customer>
<CustomerID>1</CustomerID>
<Name>Customer One</Name>
</Customer>
<Customer>
<CustomerID>2</CustomerID>
<Name>Customer Two</Name>
</Customer>
</Customers>

 
Suppose you don’t want to process all the customer’s at once, but you want to be able to process each Customer node one by one in your orchestration. This can be done by using a Envelope schema and a Document schema: the envelope (Customers node) contains any number of documents (Customer node). So let’s create these schema’s in BizTalk:

Steps for Creating the Application

1. Create document schema.
2. Create envelope schema.
3. Generate the Instance of the Envelope Schema
4. Create the Receive Pipeline
5. Create the Receive and send Ports 
6. Create Document Schema

• Start a new empty BizTalk project and add a new schema, as CustomerDocument.
• Change the name of the root node to Customer.
• Add the CustomerID and Name properties as the child field elements to the schema.

Note: Make sure that the schema looks similar to the above image, for me it created problems. The point is to set xs:int and xs:string as “Data Type” property and not “Base Data Type” property.

1. Let’s add a new schema to the project and name it “CustomersEnvelope”.
2. Identify the new schema as an envelope by selecting the schema node and changing the Envelope property to Yes in the Properties window.
3. Change the name of the root node to Customers.
4. You can import the document schema into the envelope schema by clicking on the ellipsis button for the Imports property of the schema node. You’ll get a dialog window in which you can add an “XSD Import” of the CustomerDocument schema. Then add a new child record node under the Customers node and name itCustomer. Set the Data Structure property of this new node to “ns0:Customer”. (If you haven’t changed the namespace: If you don’t like ns0 notation, and want to put proper notation, use “ab|” tag provided in the import dialog. See the image shown below for more details.)
5.  

Note: If you do not want to use an XSD Import, you can set the Data Structure property to “xs:anyType”. When you say “xs:anyType” it will remove the child nodes. That is why, this way is not preferable as it will increase the complexity.

1. Change to “Body XPath” property of the Customers node by clicking the ellipsis button and point to the Customers node. The property will be set to: /*[local-name()='Customers'and namespace-uri()='http://XMLSplitExample.CustomersEnvelope']

Note: Sometimes the ns0:Customers is not Reflected in DataStructure Property for that press Save all.Make sure that yourxPath is exactly similar to the one given above, by default it will append some other code also.

1. After Generating the Instance the output looks like 
2. Create Receive Pipeline

Next you need to configure a new ReceivePipeline in which the schemas created above will be used:

• Add a new ReceivePipeline to your project and name it EnvelopeReceivePipeline.
• Add an XML disassembler to the disassemble stage of theReceivePipeline.
• Set the Document schemas property of that XML disassembler to the CustomerDocument schema.
• Set the Envelope schemas property of the XML disassembler to the CustomersEnvelope schema. 
• Create Receive Port and Send Port

Now the CustomersReceivePipeline can be used in an orchestration; so let’s do that:

1. Add a new orchestration to the project, name the orchestration as BatchOrc.
2. Add a port to the orchestration.
3. Once you add a port to Port Surface Area, it will open the port configuration wizard as shown below:
4. Click on Next which will take you to the following screen. Give Port Name as RecvPort
5. Here tick on ‘always receiving’ and click next and Finish: 
6. In same  way create a Send Port, with option ‘I am Always Sending the Message’.
7. Now add a receive shape to the orchestration that receives a message of the CustomerDocument schema type.
8. Similarly add a send shape to the orchestration that sends a message of the CustomerDocument schema type to send port configured earlier.
9. Once everything is set, please compile and deploy the orchestration to the BizTalk server.
10. Once the deployment is successful, open the BizTalk Admin Console. Click the Refresh Button. You can see the Name of the Application that you have given in the solution Explorer. 
11. The Application Name is Envelope 
12. Create the Send Port. Set the Pipelines and Transport type. 
13. Give the URL or Path where you want to see the Output File
14. Create the Receive Port and Receive location. Select the custome PipeLine and click on the Configure and set the destination Path Enable the Receive Port, start and enlist the Send Port, Orchestration and Restart the Host Instances

 Create a sample input file like below

 

<ns0:Customers xmlns:ns0=”http://EnvelopeSample.EnvelopeSch”>

<ns1:Customer  xmlns:ns1=”http://EnvelopeSample.DocumentSch”>

<CustomerID>10</CustomerID>

<Name>Name1</Name>

</ns1:Customer>

<ns1:Customer xmlns:ns1=”http://EnvelopeSample.DocumentSch”>

<CustomerID>101</CustomerID>

<Name>Name2</Name>

</ns1:Customer>

</ns0:Customers>

15. Drop the file in ‘In’ folder and you will receive two files in the Out Folder.

Read more ...

SQL Server Exporting Data To EXCEL file || and || Importing Data From EXCEL to SQL Server

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask 
 @outputfile = 'd:\testing.xls', 
 @query = 'Select * from Database_name..SQLServerTable', 
 @colheaders =1, 
 @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)

5 To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns
(
 @db_name varchar(100),
 @table_name varchar(100), 
 @file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
 @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
 information_schema.columns
where 
 table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Read more ...

SQL XML : Saving SQL Query Results to a File

One way to save your XML query results to the file system is by using bcp (bulk copy program).

Be aware of the following before deciding to use bcp for your regular export requirements:

• bcp is a program external to SSMS. If you need to use this from within your scripts, you will need to enable xp_cmdshell. xp_cmdshell is an extended stored procedure that allows external command line processes to be executed from within SQL Server. Enabling xp_cmdshell is considered to be a big no no in terms of security because this opens up avenues for malicious attacks through SQL Server.
• Depending on how much data you need to export, you may need to batch your export to overcome rowsize limitations of bcp.

If you intend to use bcp from within SSMS, you will need to enable xp_cmdshell first, otherwise you will get the following error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

The following example walks you through enabling xp_cmdshell, and using bcp from within SSMS to save your XML query to an external file.

   1: USE AdventureWorks

   2: GO

   3:

   4: -- --------------------------------------------------------

   5: -- Enable xp_cmdshell

   6: -- --------------------------------------------------------

   7:

   8: -- Allow advanced options to be changed.

   9: EXEC sp_configure 'show advanced options', 1

  10: GO

  11:

  12: -- Update the currently configured value for advanced options.

  13: RECONFIGURE

  14: GO

  15:

  16: -- Enable xp_cmdshell

  17: EXEC sp_configure 'xp_cmdshell', 1

  18: GO

  19:

  20: -- Update the currently configured value for xp_cmdshell

  21: RECONFIGURE

  22: GO

  23:

  24: -- Disallow further advanced options to be changed.

  25: EXEC sp_configure 'show advanced options', 0

  26: GO

  27:

  28: -- Update the currently configured value for advanced options.

  29: RECONFIGURE

  30: GO

  31:

  32:

  33: -- --------------------------------------------------------

  34: -- Sample export via bcp

  35: -- --------------------------------------------------------

  36: DECLARE @FileName VARCHAR(50)

  37: DECLARE @SQLCmd   VARCHAR(500)

  38:

  39: SELECT  @FileName = 'C:\Temp\SampleXMLOutput.xml'

  40:

  41: -- in this command, we are making sure there is only one ROOT node

  42: SELECT  @SQLCmd = 'bcp ' +

  43:                         '"SELECT Resume  ' +

  44:                         ' FROM AdventureWorks.HumanResources.JobCandidate ' +

  45:                         ' FOR XML PATH(''''), ROOT(''HRResumes''), TYPE "' +

  46:                   ' queryout '  +

  47:                   @FileName +

  48:                   ' -w -T -S' + @@SERVERNAME

  49:

  50: -- display command, for visual  check

  51: SELECT @SQLCmd AS 'Command to execute'

  52:

  53: -- create the XML file

  54: EXECUTE master..xp_cmdshell @SQLCmd

  55:

  56:

Read more ...

SQL XMl Using SQL Server XMl Exists() Function to fing Element in XML

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

-- ============================================================ -- sample code on using the SQL Server xml method exist() -- several samples shown -- Donabel Santos -- ============================================================   DECLARE @xmlSnippet XML DECLARE @id SMALLINT DECLARE @value VARCHAR(20)   SET @xmlSnippet = ' <ninjaElement id="1">SQL Server Ninja</ninjaElement> <ninjaElement id="2">SharePoint Ninja</ninjaElement> <ninjaElement id="3">ASP.NET Ninja</ninjaElement> '   -- this is what we will look for SET @id    = 2 SET @value ='SQL Server Ninja'   -- note exist() will return only either : -- 1 (true) or 0 (false)   -- check if a node called ninjaElement exists -- at any level in the XML snippet SELECT @xml.exist('//ninjaElement')   -- check if a node called bar exists SELECT @xml.exist('//bar')   -- check if attribute id exists anywhere SELECT @xml.exist('//@id')   -- check if attribute id exists within a ninjaElement tag SELECT @xml.exist('//ninjaElement[@id]')   -- check if the id attribute equals to what we saved -- in the @id variable SELECT @xml.exist('/ninjaElement[@id=sql:variable("@id")]')   -- check if the node text equals to what -- we saved in the @value variable SELECT @xml.exist('/ninjaElement1')

Read more ...