Коллеги, приветствую!
Помогите побороть ошибку "Schema: mixed content is not allowed on element 'ZL_LIST'. Property elements cannot have subelements."
(Хотя, возможно, она реально в моей ДНК, т.к. нагородил я, для ее получения, много).
Итак, имеется БД, которая предназначена для загрузки некоего 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.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
USE [exp]
GO
-- Удаление всех refferential
DECLARE @SQL VARCHAR(MAX)=''
Set @SQL = (
SELECT DISTINCT N'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + N'.' + QUOTENAME(FK.TABLE_NAME) + N' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) + N'];'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
Where QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) in ('[dbo].[PACIENT]', '[dbo].[SANK]', '[dbo].[SCHET]', '[dbo].[SLUCH]', '[dbo].[USL]', '[dbo].[ZAP]', '[dbo].[ZGLV]', '[dbo].[ZL_LIST]')
For xml path('')
)
EXEC (@SQL)
GO
DROP TABLE IF EXISTS [dbo].[ZL_LIST]
GO
DROP TABLE IF EXISTS [dbo].[ZGLV]
GO
DROP TABLE IF EXISTS [dbo].[EXP]
GO
DROP TABLE IF EXISTS [dbo].[PACIENT]
GO
DROP TABLE IF EXISTS [dbo].[SCHET]
GO
DROP TABLE IF EXISTS [dbo].[ZAP]
GO
DROP TABLE IF EXISTS [dbo].[SLUCH]
GO
DROP TABLE IF EXISTS [dbo].[USL]
GO
DROP TABLE IF EXISTS [dbo].[SANK]
GO
/****** Object: Table [dbo].[ZL_LIST] Script Date: 04.05.2018 13:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ZL_LIST] (
[_ZL_LIST_ID] BIGINT identity(1, 1) NOT NULL,
-- [_FILIAL_ID] smallint NOT NULL,
[_LOAD_DATE] DATETIME NOT NULL DEFAULT Current_timestamp
,CONSTRAINT [PK_ZL_LIST] PRIMARY KEY CLUSTERED ([_ZL_LIST_ID] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ZGLV] Script Date: 04.05.2018 13:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ZGLV](
[_ZGLV_ID] bigint identity(1,1) NOT NULL,
[_ZL_LIST_ID] bigint NOT NULL,
-- [_FILIAL_ID] smallint NOT NULL,
[VERSION] nvarchar(5) NOT NULL,
[DATA] [date] NOT NULL,
[FILENAME] nvarchar(26) NOT NULL,
[EXP_COUNT] [int] NULL,
[SCHET_COUNT] [int] NOT NULL
CONSTRAINT [PK_ZGLV] PRIMARY KEY CLUSTERED
(
[_ZGLV_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[EXP] Script Date: 04.05.2018 13:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EXP](
[_EXP_ID] bigint identity(1,1) NOT NULL,
[_ZL_LIST_ID] bigint NOT NULL,
-- [_FILIAL_ID] smallint NOT NULL,
[code] nvarchar(40) NOT NULL,
[CODE_MO] nvarchar(6) NOT NULL,
[NACT] nvarchar(15) NOT NULL,
[DACT] [date] NOT NULL,
[DBEGIN] [date] NOT NULL,
[DEND] [date] NOT NULL,
[USER] nvarchar(100) NOT NULL,
[MODE] [tinyint] NOT NULL,
[SLUCH_COUNT] int NOT NULL,
[SUM] numeric(15,2) NOT NULL,
[PENALTY] numeric(15,2) NOT NULL,
[DCREATE] [date] NULL,
[DOUT] [date] NULL,
[DIN] [date] NULL,
[FES] smallint NOT NULL,
[INFO] nvarchar(255) NULL,
CONSTRAINT [PK_EXP] PRIMARY KEY CLUSTERED
(
[_EXP_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SCHET] Script Date: 04.05.2018 13:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SCHET](
[_SCHET_ID] bigint identity(1,1) NOT NULL,
[_ZL_LIST_ID] bigint NOT NULL,
-- [_FILIAL_ID] smallint NOT NULL,
[code] nvarchar(40) NOT NULL,
[CODE_MO] nvarchar(6) NOT NULL,
[YEAR] [smallint] NOT NULL,
[MONTH] [tinyint] NOT NULL,
[NSCHET] nvarchar(15) NULL,
[DSCHET] [date] NOT NULL,
[SCH_TYPE] nvarchar(2) NULL,
[SUMMAV] numeric(15,2) NOT NULL,
[SUMMA_PF] numeric(15,2) NULL,
[COMENTS] nvarchar(255) NULL,
[SUMMAP] numeric(15,2) NULL,
[SANK_MEK] numeric(15,2) NULL,
[SANK_MEE] numeric(15,2) NULL,
[SANK_EKMP] numeric(15,2) NULL,
[ZAP_COUNT] int NOT NULL,
[SLUCH_COUNT] int NOT NULL,
CONSTRAINT [PK_SCHET] PRIMARY KEY CLUSTERED
(
[_SCHET_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ZAP] Script Date: 04.05.2018 13:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ZAP](
[_ZAP_ID] bigint identity(1,1) NOT NULL,
[_SCHET_ID] bigint NOT NULL,
-- [_FILIAL_ID] smallint NOT NULL,
[N_ZAP] [int] NOT NULL,
[PR_NOV] [tinyint] NOT NULL,
CONSTRAINT [PK_ZAP] PRIMARY KEY CLUSTERED
(
[_ZAP_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[PACIENT] Script Date: 04.05.2018 13:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PACIENT](
[_PACIENT_ID] bigint identity(1,1) NOT NULL,
[_ZAP_ID] bigint NOT NULL,
-- [_FILIAL_ID] smallint NOT NULL,
[ID_PAC] nvarchar(36) NULL,
[FAM] nvarchar(40) NULL,
[IM] nvarchar(40) NULL,
[OT] nvarchar(40) NULL,
[W] [tinyint] NOT NULL,
[DR] [date] NOT NULL,
[DOST] nvarchar(255) NULL,
[FAM_P] nvarchar(40) NULL,
[IM_P] nvarchar(40) NULL,
[OT_P] nvarchar(40) NULL,
[W_P] [tinyint] NULL,
[DR_P] [date] NULL,
[DOST_P] nvarchar(255) NULL,
[MR] nvarchar(255) NULL,
[DOCTYPE] [tinyint] NULL,
[DOCSER] nvarchar(10) NULL,
[DOCNUM] nvarchar(20) NULL,
[SNILS] nvarchar(14) NULL,
[OKATO] nvarchar(11) NULL,
[COMENTP] nvarchar(250) NULL,
[VPOLIS] [tinyint] NOT NULL,
[SPOLIS] nvarchar(10) NULL,
[NPOLIS] nvarchar(20) NOT NULL,
[NOVOR] nvarchar(9) NOT NULL,
[VNOV_D] smallint NULL,
CONSTRAINT [PK_PACIENT] PRIMARY KEY CLUSTERED
(
[_PACIENT_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SLUCH] Script Date: 04.05.2018 13:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SLUCH](
[_SLUCH_ID] bigint identity(1,1) NOT NULL,
[_ZAP_ID] bigint NOT NULL,
-- [_FILIAL_ID] smallint NOT NULL,
[IDCASE] nvarchar(100) NOT NULL,
[USL_OK] [smallint] NOT NULL,
[VIDPOM] [smallint] NOT NULL,
[FOR_POM] [smallint] NULL,
[NPR_MO] nvarchar(6) NULL,
[EXTR] [smallint] NULL,
[LPU] nvarchar(6) NOT NULL,
[LPU_1] nvarchar(8) NULL,
[PODR] nvarchar(8) NULL,
[PROFIL] [smallint] NULL,
[DET] [tinyint] NOT NULL,
[NHISTORY] nvarchar(50) NULL,
[DATE_1] [date] NOT NULL,
[DATE_2] [date] NOT NULL,
[DS0] nvarchar(10) NULL,
[DS1] nvarchar(10) NULL,
[DS2] nvarchar(10) NULL,
[DS3] nvarchar(10) NULL,
[VNOV_M] smallint NULL,
[CODE_MES1] nvarchar(20) NULL,
[CODE_MES2] nvarchar(20) NULL,
[CASE_TYPE] nvarchar(2) NOT NULL,
[RSLT] [smallint] NOT NULL,
[ISHOD] [smallint] NULL,
[PRVS] [smallint] NULL,
[VID_HMP] nvarchar(18) NULL,
[METOD_HMP] nvarchar(3) NULL,
[VBR] tinyint NULL,
[P_OTK] tinyint NULL,
[DS1_PR] tinyint NULL,
[NAZR] nvarchar(255) NULL,
[NAZ_SP] nvarchar(255) NULL,
[NAZ_V] nvarchar(255) NULL,
[NAZ_PMP] nvarchar(255) NULL,
[NAZ_PK] nvarchar(255) NULL,
[PR_D_N] tinyint NULL,
[SMP_T1] nvarchar(5) NULL,
[SMP_T2] nvarchar(5) NULL,
[IDDOKT] nvarchar(128) NULL,
[OS_SLUCH] nvarchar(255) NULL,
[IDSP] [smallint] NOT NULL,
[ED_COL] numeric(5,2) NULL,
[TARIF] numeric(15,2) NULL,
[SUMV] numeric(15,2) NULL,
[OPLATA] [tinyint] NOT NULL,
[SUMP] numeric(15,2) NULL,
[SANK_IT] numeric(15,2) NULL,
[COMENTSL] nvarchar(255) NULL,
CONSTRAINT [PK_SLUCH] PRIMARY KEY CLUSTERED
(
[_SLUCH_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[USL] Script Date: 04.05.2018 13:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[USL](
[_USL_ID] bigint identity(1,1) NOT NULL,
[_SLUCH_ID] bigint NOT NULL,
-- [_FILIAL_ID] smallint NOT NULL,
[IDSERV] nvarchar(36) NOT NULL,
[LPU] nvarchar(6) NOT NULL,
[LPU_1] nvarchar(8) NULL,
[PODR] nvarchar(8) NULL,
[PROFIL] [smallint] NULL,
[VID_VME] nvarchar(15) NULL,
[DET] [tinyint] NOT NULL,
[DATE_IN] [date] NOT NULL,
[DATE_OUT] [date] NOT NULL,
[DS] nvarchar(10) NULL,
[CODE_USL] nvarchar(20) NULL,
[KOL_USL] numeric(6,2) NOT NULL,
[TARIF] numeric(15,2) NULL,
[SUMV_USL] numeric(15,2) NULL,
[PRVS] [int] NULL,
[IDDOKT] nvarchar(128) NULL,
[P_OTK] [tinyint] NULL,
[COMENTU] nvarchar(255) NULL,
CONSTRAINT [PK_USL] PRIMARY KEY CLUSTERED
(
[_USL_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SANK] Script Date: 04.05.2018 13:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SANK](
[_SANK_ID] bigint identity(1,1) NOT NULL,
[_SLUCH_ID] bigint NOT NULL,
-- [_FILIAL_ID] smallint NOT NULL,
[S_CODE] nvarchar(40) NOT NULL,
[S_SUM] numeric(15,2) NOT NULL,
[S_TIP] [tinyint] NOT NULL,
[S_OSN] [smallint] NULL,
[S_COM] nvarchar(255) NULL,
[S_IST] [tinyint] NOT NULL,
[EXP_CODE] nvarchar(40) NULL,
[EXP_DOCTOR] nvarchar(128) NULL,
[EXP_FILTER] nvarchar(255) NULL,
[EXP_PENALTY] numeric(15,2) NULL,
[EXP_PERCENT] numeric(3,2) NULL,
[EXP_INFO] nvarchar(255)
CONSTRAINT [PK_SANK] PRIMARY KEY CLUSTERED
(
[_SANK_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE dbo.SCHET ADD CONSTRAINT
FK_SCHET_ZL_LIST FOREIGN KEY
(
_ZL_LIST_ID
) REFERENCES dbo.ZL_LIST
(
_ZL_LIST_ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.ZAP ADD CONSTRAINT
FK_ZAP_SCHET FOREIGN KEY
(
_SCHET_ID
) REFERENCES dbo.SCHET
(
_SCHET_ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.SLUCH ADD CONSTRAINT
FK_SLUCH_ZAP FOREIGN KEY
(
_ZAP_ID
) REFERENCES dbo.ZAP
(
_ZAP_ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.SANK ADD CONSTRAINT
FK_SANK_SLUCH FOREIGN KEY
(
_SLUCH_ID
) REFERENCES dbo.SLUCH
(
_SLUCH_ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.PACIENT ADD CONSTRAINT
FK_PACIENT_ZAP FOREIGN KEY
(
_ZAP_ID
) REFERENCES dbo.ZAP
(
_ZAP_ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.ZGLV ADD CONSTRAINT
FK_ZGLV_ZL_LIST FOREIGN KEY
(
_ZL_LIST_ID
) REFERENCES dbo.ZL_LIST
(
_ZL_LIST_ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.USL ADD CONSTRAINT
FK_USL_SLUCH FOREIGN KEY
(
_SLUCH_ID
) REFERENCES dbo.SLUCH
(
_SLUCH_ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.EXP ADD CONSTRAINT
FK_EXP_ZL_LIST FOREIGN KEY
(
_ZL_LIST_ID
) REFERENCES dbo.ZL_LIST
(
_ZL_LIST_ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
Я создаю для нее аннотированную схему, для загрузки с помощью sqlxmlbulkload.
Причем, т.к. описывать всё каждый раз мне лень, а адекватного инструмента по созданию таких схем я не знаю - я написал запрос, который делает для меня почти готовый полуфабрикат для такой загрузки:
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.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
Use [exp]
go
;WITH XMLNAMESPACES (
'http://www.w3.org/2001/XMLSchema' AS xs
,'urn:schemas-microsoft-com:mapping-schema' AS sql
,'http://schemas.microsoft.com/sqlserver/2004/sqltypes' as sqltypes
)
Select
Cast(
N'<xs:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" elementFormDefault="qualified">'
+ N'<xs:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />'
+ (Select
Replace (
Replace (
Replace (
(Select
(Select
(
SELECT f.[name] [@name]
,OBJECT_NAME(f.referenced_object_id) [@parent]
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) [@parent-key]
,OBJECT_NAME(f.parent_object_id) [@child]
,COL_NAME(fc.parent_object_id, fc.parent_column_id) [@child-key]
FROM sys.foreign_keys f
INNER JOIN sys.foreign_key_columns fc ON f.object_id = fc.constraint_object_id
Order by f.referenced_object_id
For xml path('sql:relationship'), type
)
For xml path('xs:appinfo'), type
)
For xml path('xs:annotation')
)
+(
SELECT
a.[name] [@name]
,(Select
(Select Case when t.[name] in (N'varchar', N'nvarchar', N'char', N'nchar', N'varbinary', N'binary')
then
(Select b.[name] [@name]
,Quotename(b.[name]) [@sql:field]
,t.[name] + '(' + iif(b.max_length = -1, 'max', Cast(b.max_length/iif(t.[name] in (N'nvarchar', N'nchar'), 2, 1) as nvarchar(10)) + ')') [@sql:datatype]
,Case when b.is_nullable=1 then 0 end [@minOccurs]
,(Select
(Select
'sqltypes:' + t.[name] [@base]
,(Select iif(b.max_length = -1, NULL, b.max_length)/iif(t.[name] in (N'nvarchar', N'nchar'), 2, 1) [@value] for xml path('xs:maxLength'), type)
for xml path('xs:restriction'), type
)
for xml path('xs:simpleType'), type
)
for xml path('xs:element'), type
)
when t.[name] in (N'numeric', N'decimal')
then
(Select b.[name] [@name]
,Quotename(b.[name]) [@sql:field]
,t.[name] [@sql:datatype]
,Case when b.is_nullable=1 then 0 end [@minOccurs]
,(Select
(Select
'sqltypes:' + t.[name] [@base]
,(Select b.[precision] [@value] for xml path('xs:totalDigits'), type)
,(Select b.[scale] [@value] for xml path('xs:fractionDigits'), type)
for xml path('xs:restriction'), type
)
for xml path('xs:simpleType'), type
)
for xml path('xs:element'), type
)
Else (Select b.[name] [@name]
,Quotename(b.[name]) [@sql:field]
,'sqltypes:' + t.[name] [@type]
,Case when b.is_nullable=1 then 0 end [@minOccurs]
,t.[name] [@sql:datatype]
for xml path('xs:element'), type
)
End
from
sys.columns b
inner join sys.types t on b.user_type_id=t.user_type_id
Where a.[object_id]=b.[object_id]
and b.[name] not like '[_]%'
Order by b.column_id ASC
For xml path(''), type
)
,(Select
OBJECT_NAME(f.parent_object_id) [@name]
,OBJECT_NAME(f.parent_object_id) [@type]
,OBJECT_NAME(f.parent_object_id) [@sql:relation]
,f.[name] [@sql:relationship]
,COL_NAME(fc.parent_object_id, fc.parent_column_id) [@sql:field]
,0 [@minOccurs]
,'unbounded' [@maxOccurs]
from sys.foreign_keys f
INNER JOIN sys.foreign_key_columns fc
ON f.object_id = fc.constraint_object_id
where f.referenced_object_id = a.object_id
for xml path('xs:element'), type
)
For xml path('xs:sequence'), type
)
FROM sys.tables a
WHERE a.[type] = 'U'
AND a.[name] <> 'sysdiagrams'
FOR XML path ('xs:complexType')
)
---------------------------------------------------------------------------------------------
,' xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"', ''
)
,' xmlns:sql="urn:schemas-microsoft-com:mapping-schema"', ''
)
,' xmlns:xs="http://www.w3.org/2001/XMLSchema"', ''
)
)
+ N'</xs:schema>'
as xml)
Сам запрос - тоже полуфабрикат, так что сильно - не пинайте.
... Хотя нет, пинайте, полезно будет.
С помощью него я получаю почти готовую, как мне кажется, схему для проверки 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.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
484.
485.
486.
487.
488.
489.
490.
491.
492.
493.
494.
495.
496.
497.
498.
499.
500.
501.
502.
503.
504.
505.
506.
507.
508.
509.
510.
511.
512.
513.
514.
515.
516.
517.
518.
519.
520.
521.
522.
523.
524.
525.
526.
527.
528.
529.
530.
531.
532.
533.
534.
535.
536.
537.
538.
539.
540.
541.
542.
543.
544.
545.
546.
547.
548.
549.
550.
551.
552.
553.
554.
555.
556.
557.
558.
559.
560.
561.
562.
563.
564.
565.
566.
567.
568.
569.
570.
571.
572.
573.
574.
575.
576.
577.
578.
579.
580.
581.
582.
583.
584.
585.
586.
587.
588.
589.
590.
591.
592.
593.
594.
595.
596.
597.
598.
599.
600.
601.
602.
603.
604.
605.
606.
607.
608.
609.
610.
611.
612.
613.
614.
615.
616.
617.
618.
619.
620.
621.
622.
623.
624.
625.
626.
627.
628.
629.
630.
631.
632.
633.
634.
635.
636.
637.
638.
639.
640.
641.
642.
643.
644.
645.
646.
647.
648.
649.
650.
651.
652.
653.
654.
655.
656.
657.
658.
659.
660.
661.
662.
663.
664.
665.
666.
667.
668.
669.
670.
671.
672.
673.
674.
675.
676.
677.
678.
679.
680.
681.
682.
683.
684.
685.
686.
687.
688.
689.
690.
691.
692.
693.
694.
695.
696.
697.
698.
699.
700.
701.
702.
703.
704.
705.
706.
707.
708.
709.
710.
711.
712.
713.
714.
715.
716.
717.
718.
719.
720.
721.
722.
723.
724.
725.
726.
727.
728.
729.
730.
731.
732.
733.
734.
735.
736.
737.
738.
739.
740.
741.
742.
743.
744.
745.
746.
747.
748.
749.
750.
751.
752.
753.
754.
755.
<xs:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" elementFormDefault="qualified">
<xs:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xs:annotation>
<xs:appinfo>
<sql:relationship name="FK_SCHET_ZL_LIST" parent="ZL_LIST" parent-key="_ZL_LIST_ID" child="SCHET" child-key="_ZL_LIST_ID" />
<sql:relationship name="FK_ZGLV_ZL_LIST" parent="ZL_LIST" parent-key="_ZL_LIST_ID" child="ZGLV" child-key="_ZL_LIST_ID" />
<sql:relationship name="FK_EXP_ZL_LIST" parent="ZL_LIST" parent-key="_ZL_LIST_ID" child="EXP" child-key="_ZL_LIST_ID" />
<sql:relationship name="FK_ZAP_SCHET" parent="SCHET" parent-key="_SCHET_ID" child="ZAP" child-key="_SCHET_ID" />
<sql:relationship name="FK_SLUCH_ZAP" parent="ZAP" parent-key="_ZAP_ID" child="SLUCH" child-key="_ZAP_ID" />
<sql:relationship name="FK_PACIENT_ZAP" parent="ZAP" parent-key="_ZAP_ID" child="PACIENT" child-key="_ZAP_ID" />
<sql:relationship name="FK_SANK_SLUCH" parent="SLUCH" parent-key="_SLUCH_ID" child="SANK" child-key="_SLUCH_ID" />
<sql:relationship name="FK_USL_SLUCH" parent="SLUCH" parent-key="_SLUCH_ID" child="USL" child-key="_SLUCH_ID" />
</xs:appinfo>
</xs:annotation>
<xs:complexType name="ZL_LIST" mixed="true">
<xs:sequence>
<xs:element name="ZGLV" type="ZGLV" sql:relation="ZGLV" sql:relationship="FK_ZGLV_ZL_LIST" sql:field="_ZL_LIST_ID" minOccurs="0" maxOccurs="unbounded" />
<xs:element name="EXP" type="EXP" sql:relation="EXP" sql:relationship="FK_EXP_ZL_LIST" sql:field="_ZL_LIST_ID" minOccurs="0" maxOccurs="unbounded" />
<xs:element name="SCHET" type="SCHET" sql:relation="SCHET" sql:relationship="FK_SCHET_ZL_LIST" sql:field="_ZL_LIST_ID" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="ZGLV">
<xs:sequence>
<xs:element name="VERSION" sql:field="[VERSION]" sql:datatype="nvarchar(5)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DATA" sql:field="[DATA]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="FILENAME" sql:field="[FILENAME]" sql:datatype="nvarchar(26)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="26" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="EXP_COUNT" sql:field="[EXP_COUNT]" type="sqltypes:int" minOccurs="0" sql:datatype="int" />
<xs:element name="SCHET_COUNT" sql:field="[SCHET_COUNT]" type="sqltypes:int" sql:datatype="int" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="EXP">
<xs:sequence>
<xs:element name="CODE" sql:field="[code]" sql:datatype="nvarchar(40)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="CODE_MO" sql:field="[CODE_MO]" sql:datatype="nvarchar(6)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="6" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="NACT" sql:field="[NACT]" sql:datatype="nvarchar(15)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="15" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DACT" sql:field="[DACT]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="DBEGIN" sql:field="[DBEGIN]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="DEND" sql:field="[DEND]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="USER" sql:field="[USER]" sql:datatype="nvarchar(100)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="100" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="MODE" sql:field="[MODE]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="SLUCH_COUNT" sql:field="[SLUCH_COUNT]" type="sqltypes:int" sql:datatype="int" />
<xs:element name="SUM" sql:field="[SUM]" sql:datatype="numeric">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="PENALTY" sql:field="[PENALTY]" sql:datatype="numeric">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DCREATE" sql:field="[DCREATE]" type="sqltypes:date" minOccurs="0" sql:datatype="date" />
<xs:element name="DOUT" sql:field="[DOUT]" type="sqltypes:date" minOccurs="0" sql:datatype="date" />
<xs:element name="DIN" sql:field="[DIN]" type="sqltypes:date" minOccurs="0" sql:datatype="date" />
<xs:element name="FES" sql:field="[FES]" type="sqltypes:smallint" sql:datatype="smallint" />
<xs:element name="INFO" sql:field="[INFO]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="SCHET">
<xs:sequence>
<xs:element name="CODE" sql:field="[code]" sql:datatype="nvarchar(40)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="CODE_MO" sql:field="[CODE_MO]" sql:datatype="nvarchar(6)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="6" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="YEAR" sql:field="[YEAR]" type="sqltypes:smallint" sql:datatype="smallint" />
<xs:element name="MONTH" sql:field="[MONTH]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="NSCHET" sql:field="[NSCHET]" sql:datatype="nvarchar(15)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="15" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DSCHET" sql:field="[DSCHET]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="SCH_TYPE" sql:field="[SCH_TYPE]" sql:datatype="nvarchar(2)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SUMMAV" sql:field="[SUMMAV]" sql:datatype="numeric">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SUMMA_PF" sql:field="[SUMMA_PF]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="COMENTS" sql:field="[COMENTS]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SUMMAP" sql:field="[SUMMAP]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SANK_MEK" sql:field="[SANK_MEK]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SANK_MEE" sql:field="[SANK_MEE]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SANK_EKMP" sql:field="[SANK_EKMP]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ZAP_COUNT" sql:field="[ZAP_COUNT]" type="sqltypes:int" sql:datatype="int" />
<xs:element name="SLUCH_COUNT" sql:field="[SLUCH_COUNT]" type="sqltypes:int" sql:datatype="int" />
<xs:element name="ZAP" type="ZAP" sql:relation="ZAP" sql:relationship="FK_ZAP_SCHET" sql:field="_SCHET_ID" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="ZAP">
<xs:sequence>
<xs:element name="N_ZAP" sql:field="[N_ZAP]" type="sqltypes:int" sql:datatype="int" />
<xs:element name="PR_NOV" sql:field="[PR_NOV]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="PACIENT" type="PACIENT" sql:relation="PACIENT" sql:relationship="FK_PACIENT_ZAP" sql:field="_ZAP_ID" minOccurs="0" maxOccurs="unbounded" />
<xs:element name="SLUCH" type="SLUCH" sql:relation="SLUCH" sql:relationship="FK_SLUCH_ZAP" sql:field="_ZAP_ID" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="PACIENT">
<xs:sequence>
<xs:element name="ID_PAC" sql:field="[ID_PAC]" sql:datatype="nvarchar(36)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="36" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="FAM" sql:field="[FAM]" sql:datatype="nvarchar(40)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="IM" sql:field="[IM]" sql:datatype="nvarchar(40)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OT" sql:field="[OT]" sql:datatype="nvarchar(40)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="W" sql:field="[W]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="DR" sql:field="[DR]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="DOST" sql:field="[DOST]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="FAM_P" sql:field="[FAM_P]" sql:datatype="nvarchar(40)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="IM_P" sql:field="[IM_P]" sql:datatype="nvarchar(40)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OT_P" sql:field="[OT_P]" sql:datatype="nvarchar(40)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="W_P" sql:field="[W_P]" type="sqltypes:tinyint" minOccurs="0" sql:datatype="tinyint" />
<xs:element name="DR_P" sql:field="[DR_P]" type="sqltypes:date" minOccurs="0" sql:datatype="date" />
<xs:element name="DOST_P" sql:field="[DOST_P]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="MR" sql:field="[MR]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DOCTYPE" sql:field="[DOCTYPE]" type="sqltypes:tinyint" minOccurs="0" sql:datatype="tinyint" />
<xs:element name="DOCSER" sql:field="[DOCSER]" sql:datatype="nvarchar(10)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DOCNUM" sql:field="[DOCNUM]" sql:datatype="nvarchar(20)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="20" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SNILS" sql:field="[SNILS]" sql:datatype="nvarchar(14)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="14" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OKATO" sql:field="[OKATO]" sql:datatype="nvarchar(11)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="11" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="COMENTP" sql:field="[COMENTP]" sql:datatype="nvarchar(250)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="250" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="VPOLIS" sql:field="[VPOLIS]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="SPOLIS" sql:field="[SPOLIS]" sql:datatype="nvarchar(10)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="NPOLIS" sql:field="[NPOLIS]" sql:datatype="nvarchar(20)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="20" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="NOVOR" sql:field="[NOVOR]" sql:datatype="nvarchar(9)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="9" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="VNOV_D" sql:field="[VNOV_D]" type="sqltypes:smallint" minOccurs="0" sql:datatype="smallint" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="SLUCH">
<xs:sequence>
<xs:element name="IDCASE" sql:field="[IDCASE]" sql:datatype="nvarchar(100)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="100" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="USL_OK" sql:field="[USL_OK]" type="sqltypes:smallint" sql:datatype="smallint" />
<xs:element name="VIDPOM" sql:field="[VIDPOM]" type="sqltypes:smallint" sql:datatype="smallint" />
<xs:element name="FOR_POM" sql:field="[FOR_POM]" type="sqltypes:smallint" minOccurs="0" sql:datatype="smallint" />
<xs:element name="NPR_MO" sql:field="[NPR_MO]" sql:datatype="nvarchar(6)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="6" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="EXTR" sql:field="[EXTR]" type="sqltypes:smallint" minOccurs="0" sql:datatype="smallint" />
<xs:element name="LPU" sql:field="[LPU]" sql:datatype="nvarchar(6)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="6" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="LPU_1" sql:field="[LPU_1]" sql:datatype="nvarchar(8)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="8" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="PODR" sql:field="[PODR]" sql:datatype="nvarchar(8)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="8" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="PROFIL" sql:field="[PROFIL]" type="sqltypes:smallint" minOccurs="0" sql:datatype="smallint" />
<xs:element name="DET" sql:field="[DET]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="NHISTORY" sql:field="[NHISTORY]" sql:datatype="nvarchar(50)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DATE_1" sql:field="[DATE_1]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="DATE_2" sql:field="[DATE_2]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="DS0" sql:field="[DS0]" sql:datatype="nvarchar(10)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DS1" sql:field="[DS1]" sql:datatype="nvarchar(10)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DS2" sql:field="[DS2]" sql:datatype="nvarchar(10)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DS3" sql:field="[DS3]" sql:datatype="nvarchar(10)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="VNOV_M" sql:field="[VNOV_M]" type="sqltypes:smallint" minOccurs="0" sql:datatype="smallint" />
<xs:element name="CODE_MES1" sql:field="[CODE_MES1]" sql:datatype="nvarchar(20)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="20" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="CODE_MES2" sql:field="[CODE_MES2]" sql:datatype="nvarchar(20)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="20" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="CASE_TYPE" sql:field="[CASE_TYPE]" sql:datatype="nvarchar(2)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="RSLT" sql:field="[RSLT]" type="sqltypes:smallint" sql:datatype="smallint" />
<xs:element name="ISHOD" sql:field="[ISHOD]" type="sqltypes:smallint" minOccurs="0" sql:datatype="smallint" />
<xs:element name="PRVS" sql:field="[PRVS]" type="sqltypes:smallint" minOccurs="0" sql:datatype="smallint" />
<xs:element name="VID_HMP" sql:field="[VID_HMP]" sql:datatype="nvarchar(18)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="18" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="METOD_HMP" sql:field="[METOD_HMP]" sql:datatype="nvarchar(3)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="3" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="VBR" sql:field="[VBR]" type="sqltypes:tinyint" minOccurs="0" sql:datatype="tinyint" />
<xs:element name="P_OTK" sql:field="[P_OTK]" type="sqltypes:tinyint" minOccurs="0" sql:datatype="tinyint" />
<xs:element name="DS1_PR" sql:field="[DS1_PR]" type="sqltypes:tinyint" minOccurs="0" sql:datatype="tinyint" />
<xs:element name="NAZR" sql:field="[NAZR]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="NAZ_SP" sql:field="[NAZ_SP]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="NAZ_V" sql:field="[NAZ_V]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="NAZ_PMP" sql:field="[NAZ_PMP]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="NAZ_PK" sql:field="[NAZ_PK]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="PR_D_N" sql:field="[PR_D_N]" type="sqltypes:tinyint" minOccurs="0" sql:datatype="tinyint" />
<xs:element name="SMP_T1" sql:field="[SMP_T1]" sql:datatype="nvarchar(5)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SMP_T2" sql:field="[SMP_T2]" sql:datatype="nvarchar(5)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="IDDOKT" sql:field="[IDDOKT]" sql:datatype="nvarchar(128)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="128" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OS_SLUCH" sql:field="[OS_SLUCH]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="IDSP" sql:field="[IDSP]" type="sqltypes:smallint" sql:datatype="smallint" />
<xs:element name="ED_COL" sql:field="[ED_COL]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="5" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="TARIF" sql:field="[TARIF]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SUMV" sql:field="[SUMV]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OPLATA" sql:field="[OPLATA]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="SUMP" sql:field="[SUMP]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SANK_IT" sql:field="[SANK_IT]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="COMENTSL" sql:field="[COMENTSL]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="USL" type="USL" sql:relation="USL" sql:relationship="FK_USL_SLUCH" sql:field="_SLUCH_ID" minOccurs="0" maxOccurs="unbounded" />
<xs:element name="SANK" type="SANK" sql:relation="SANK" sql:relationship="FK_SANK_SLUCH" sql:field="_SLUCH_ID" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="USL">
<xs:sequence>
<xs:element name="IDSERV" sql:field="[IDSERV]" sql:datatype="nvarchar(36)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="36" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="LPU" sql:field="[LPU]" sql:datatype="nvarchar(6)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="6" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="LPU_1" sql:field="[LPU_1]" sql:datatype="nvarchar(8)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="8" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="PODR" sql:field="[PODR]" sql:datatype="nvarchar(8)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="8" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="PROFIL" sql:field="[PROFIL]" type="sqltypes:smallint" minOccurs="0" sql:datatype="smallint" />
<xs:element name="VID_VME" sql:field="[VID_VME]" sql:datatype="nvarchar(15)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="15" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DET" sql:field="[DET]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="DATE_IN" sql:field="[DATE_IN]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="DATE_OUT" sql:field="[DATE_OUT]" type="sqltypes:date" sql:datatype="date" />
<xs:element name="DS" sql:field="[DS]" sql:datatype="nvarchar(10)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="CODE_USL" sql:field="[CODE_USL]" sql:datatype="nvarchar(20)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="20" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="KOL_USL" sql:field="[KOL_USL]" sql:datatype="numeric">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="6" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="TARIF" sql:field="[TARIF]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SUMV_USL" sql:field="[SUMV_USL]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="PRVS" sql:field="[PRVS]" type="sqltypes:int" minOccurs="0" sql:datatype="int" />
<xs:element name="IDDOKT" sql:field="[IDDOKT]" sql:datatype="nvarchar(128)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="128" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="P_OTK" sql:field="[P_OTK]" type="sqltypes:tinyint" minOccurs="0" sql:datatype="tinyint" />
<xs:element name="COMENTU" sql:field="[COMENTU]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="SANK">
<xs:sequence>
<xs:element name="S_CODE" sql:field="[S_CODE]" sql:datatype="nvarchar(40)">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="S_SUM" sql:field="[S_SUM]" sql:datatype="numeric">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="S_TIP" sql:field="[S_TIP]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="S_OSN" sql:field="[S_OSN]" type="sqltypes:smallint" minOccurs="0" sql:datatype="smallint" />
<xs:element name="S_COM" sql:field="[S_COM]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="S_IST" sql:field="[S_IST]" type="sqltypes:tinyint" sql:datatype="tinyint" />
<xs:element name="EXP_CODE" sql:field="[EXP_CODE]" sql:datatype="nvarchar(40)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="EXP_DOCTOR" sql:field="[EXP_DOCTOR]" sql:datatype="nvarchar(128)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="128" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="EXP_FILTER" sql:field="[EXP_FILTER]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="EXP_PENALTY" sql:field="[EXP_PENALTY]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="15" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="EXP_PERCENT" sql:field="[EXP_PERCENT]" sql:datatype="numeric" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:numeric">
<xs:totalDigits value="3" />
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="EXP_INFO" sql:field="[EXP_INFO]" sql:datatype="nvarchar(255)" minOccurs="0">
<xs:simpleType>
<xs:restriction base="sqltypes:nvarchar">
<xs:maxLength value="255" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:element name="ZL_LIST" type="ZL_LIST" sql:relation="ZL_LIST" sql:field="_ZL_LIST_ID" />
</xs:schema>
Допиливание, собственно, заключается в добавлении корневого элемента, и кое-где - в том, что я меняю местами некоторые элементы в соответствии с их порядком в хмл.
В результате - хмли проходят валидацию по этой схеме, но данные - не грузятся, выдаётся сабжевая ошибка.
Набор таблиц из схемы - также не создается.
Помогите понять, в чем дело!
Что я генерирую не так?
Не исключено, что всё, конечно, тогда пните в нужном направлении! |
