how to produce xsd - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to produce xsd

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-23 : 14:29:29

Hi

I would like to create an xsd file for over 20 tables. Is there a dynamic way of creating the core of the xml using a query I can then copy and save to an xsd file? I want to use this to do bulk insert using SQLXMLBulkLoad.SQLXMLBulkload.4.0. I need to produce something like this.


N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema id="GalaxyLegal" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="FileIdentifier" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="FileInfo">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Description" type="xs:string" minOccurs="0" />
              <xs:element name="CreationDateTime" type="xs:string" minOccurs="0" />
              <xs:element name="Origin" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Case">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Matter" minOccurs="0" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="LookupClientId" type="xs:string" minOccurs="0" />
                    <xs:element name="AutogenerateMatterId" type="xs:string" minOccurs="0" />
                    <xs:element name="MatterReference" type="xs:string" minOccurs="0" />
                    <xs:element name="MatterSearch" type="xs:string" minOccurs="0" />
                    <xs:element name="MatterDescription" type="xs:string" minOccurs="0" />
                    <xs:element name="LookupAccountingGroupCode" type="xs:string" minOccurs="0" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>' ;

Thanks

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-23 : 18:15:51

I didn't quite follow what you meant by "core of the xml" and "dynamic way". You may already be doing what I am describing below - if you are looking for a more automated way, you could try raw or auto directives, but then you will have much less flexibility.

Step 1. Write the query to select all the columns you want joining the tables you need etc. For example:

select EmployeeId, EmployeeName, Title, NodeLevel from #tmpEmployees

2. Add the xml path clause as in

select EmployeeId, EmployeeName, Title, NodeLevel from #tmpEmployees 
for xml path('Employee'), Root('Employees');

3. Click the xml results in the grid view and while in the xml window that comes up, use XML -> Create XML Schema from the menu bar.

4. Doing this will give you the xsd, but it will dump all the elements in a single level. If you need to nest the elements, use attributes etc., you can nest the selects, in the query, for example like this:

create table #tmpEmployees(EmployeeId int, EmployeeName varchar(255), Title varchar(255), NodeLevel int);
insert into #tmpEmployees values(1,'Sunita Beck', 'President and CEO', 1);
insert into #tmpEmployees values (2,'Yosiaz', 'CFO', 2);

select
   EmployeeId as  [@IDAttribute],
   (
      select
      EmployeeId,
      EmployeeName for xml path(''), type
   ) as EmployeeBasicInfo,
   (
      select
         Title,
         NodeLevel
      for xml path(''), type
   ) as EmployeeOrgChartInfo
from
   #tmpEmployees
for 
   xml path('Employee'), Root('Employees');
   
drop table #tmpEmployees;

Like I said earlier, you may be doing all these already. If you are, ignore this posting - I don't know a more automated way. Regardless, I hope you didn't mind that I made myself the CEO and made you report to me in my sample data *grin*

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-24 : 11:03:38

:) Sunita I did try something like that but I opted for something like this and it gets me what I want. The other approaches did not produce the exact xsd I wanted


declare @xml_header NVARCHAR(max)
declare @xml_body NVARCHAR(max)
declare @xml_footer NVARCHAR(max)
declare @xmldoc NVARCHAR(max)
DECLARE @root NVARCHAR(50)

DROP TABLE dbo.myxml

CREATE TABLE myxml(myXML XML, table_name NVARCHAR(50))        

SELECT @xml_footer =
'			  </xs:sequence>
			</xs:complexType>
		  </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>					  
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xsd:schema>'

SET @xml_body = ''


declare @tablename nvarchar(50)



DECLARE Brazil_Will_win CURSOR FOR
  SELECT TABLE_NAME
  FROM [Staging].[INFORMATION_SCHEMA].TABLES
  WHERE TABLE_NAME LIKE 'stg%'

OPEN Brazil_Will_win

FETCH NEXT FROM Brazil_Will_win INTO @tablename
WHILE @@FETCH_STATUS = 0
    BEGIN
		SET @xml_body = ''
		SET @root =  REPLACE(REPLACE(REPLACE(@tablename,'stg',''),'_',''),'s','')
		SELECT @xml_header =
		'<?xml version="1.0"?>
		<xsd:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
					xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
		  <xs:element name="Data" sql:is-constant="1">
			<xs:complexType>
			  <xs:sequence>
				<xs:element name="FileInfo" sql:is-constant="1">
				  <xs:complexType>
					<xs:sequence>
					  <xs:element minOccurs="0" name="Copyright" sql:is-constant="1" />
					</xs:sequence>
				  </xs:complexType>
				</xs:element>
				<xs:element minOccurs="0" name=Product"' + @root + 's" sql:is-constant="1">
				  <xs:complexType>
					<xs:sequence>
				  <xs:element minOccurs="0" name=Product sql:relation="' + @tablename + '" maxOccurs="unbounded" >
					<xs:complexType>
					  <xs:sequence>'


		SELECT @xml_body = @xml_body + '<xs:element minOccurs="0" name="' + [COLUMN_NAME] + '" sql:field="' + [COLUMN_NAME] + '" type="' + 
			   CASE [DATA_TYPE]
				  WHEN 'nvarchar' THEN 'xs:string'
				  WHEN 'int' THEN 'xs:integer'
				  WHEN 'datetime' THEN 'xs:integer'
				  WHEN 'decimal' THEN 'xs:integer'
				  WHEN 'varchar' THEN 'xs:string'
				  ELSE 'xs:unknown'         
			   END   + '" />'
		  FROM [Staging].[INFORMATION_SCHEMA].[COLUMNS]
		  WHERE TABLE_NAME = @tablename
		  
		SET @xmldoc  = @xml_header + @xml_body + @xml_footer
	PRINT @xmldoc

		INSERT INTO myxml
		SELECT CAST(@xmldoc AS XML), @tablename 
		

        FETCH NEXT FROM Brazil_Will_win INTO @tablename
    END
CLOSE Brazil_Will_win
DEALLOCATE Brazil_Will_win




SELECT * FROM myxml

poor man's version of xsd generator?

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-24 : 14:55:29

Glad you were able to make it work with "Brazil_Will_win" cursor :--)
And glad that you didn't get offended by my Organization chart!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources