VPF::xml из ms SQL - Форум программистов

faraon25

Дата 12.12.2016, 20:37 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 2
Регистрация: 12.12.2016

Репутация: нет
Всего: нет

Помогите пожалуйста, нужно из селекта к базе данных получить xml файл определенной структуры. я сделал но чуть не так как надо

Разметка XML

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:

<?xml version="1.0" encoding="Windows-1251"?>

<ZL_LIST>

<ZGLV>

<OKATO_OMS>35000</OKATO_OMS>

</ZGLV>

<SCHET>

<SANK_MEK>1362435.65</SANK_MEK>

<SANK_MEE>0.00</SANK_MEE>

<SANK_EKMP>0.00</SANK_EKMP>

</SCHET>

<ZAP>

<N_ZAP>398</N_ZAP>

</PACIENT>

<SLUCH>

<ED_COL>1.00</ED_COL>

</SLUCH>

</ZAP>

<ZAP>

<N_ZAP>1055</N_ZAP>

</PACIENT>

<SLUCH>

<ED_COL>1.00</ED_COL>

</SLUCH>

</ZAP>

<ZAP>

<N_ZAP>625</N_ZAP>

</PACIENT>

<SLUCH>

<ED_COL>1.00</ED_COL>

<SANK_IT>179.41</SANK_IT>

<COMENTSL>Нарушения, связанные с определением принадлежности застрахованного лица к страховой медицинской организации: введение в реестр счетов недостоверных персональных данных застрахованного лица, приводящее к невозможности его полной идентификации (ошибки </COMENTSL>

<SANK>

<S_CODE>BD2EA917-98D7-4728-95ED-49ED149AEA4A</S_CODE>

<S_SUM>179.41</S_SUM>

<S_TIP>1</S_TIP>

<S_OSN>57</S_OSN>

<S_COM>Нарушения, связанные с определением принадлежности застрахованного лица к страховой медицинской организации: введение в реестр счетов недостоверных персональных данных застрахованного лица, приводящее к невозможности его полной идентификации (ошибки </S_COM>

<S_IST>2</S_IST>

</SANK>

</SLUCH>

<SLUCH>

<ED_COL>1.00</ED_COL>

<SANK_IT>179.41</SANK_IT>

<SANK>

<S_CODE>CE619B60-6CDB-4120-881D-CFCB8C9D7742</S_CODE>

<S_SUM>179.41</S_SUM>

<S_TIP>1</S_TIP>

<S_OSN>57</S_OSN>

<S_IST>2</S_IST>

</SANK>

</SLUCH>

</ZAP>

<ZAP>

<N_ZAP>797</N_ZAP>

</PACIENT>

<SLUCH>

<ED_COL>1.00</ED_COL>

</SLUCH>

</ZAP>

нужно такую структуру как выше
а у меня в запросе

Разметка XML

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:

select

--ZGLV

o.VER_ID as 'ZGLV/VERSION',

o.XML_DATE AS 'ZGLV/DATA',

o.XML_NAME AS 'ZGLV/NAME',

--SCHET

o.CODE AS 'SCHET/CODE',

o.CODE_MO AS 'SCHET/CODE_MO',

o.YEAR AS 'SCHET/YEAR',

o.MONTH as 'SCHET/MONTH',

o.NSCHET as 'SCHET/NSCHET',

o.DSCHET as 'SCHET/DSCHET',

o.PLAT AS 'SCHET/PLAT',

o.SUMMAV AS 'SCHET/SUMMAV',

o.COMENTS AS 'SCHET/COMENTS',

o.SUMMAP AS 'SCHET/NSCHET',

o.SUMMA_PF AS 'SCHET/SUMMA_PF',

o.SANK_MEK AS 'SCHET/SANK_MEK',

o.SANK_MEE AS 'SCHET/SANK_MEE',

o.SANK_EKMP AS 'SCHET/SANK_EKMP',

--ZAP

(select

z.N_ZAP AS 'N_ZAP',

z.PR_NOV AS 'PR_NOW',

--PACIENT

(select

p.ID_PAC as 'ID_PAC',

z.VPOLIS as 'VPOLIS',

z.SPOLIS as 'SPOLIS',

z.NPOLIS as 'NPOLIS',

--st_okato????

z.SMO as 'SMO',

z.SMO_OGRN as 'SMO_OGRN',

--SMO_OK?????

--SMO_NAM??

z.NOVOR as 'NOVOR',

z.VNOV_D as 'VNOV_D'

FOR

XML PATH('PACIENT'),

TYPE),

--SLUCH

(select

t.IDCASE as 'IDCASE',

t.USL_OK as 'USL_OK',

t.VIDPOM as 'VIDPOM',

t.FOR_POM as 'FOR_POM',

-- t.NPR_N as 't.NPR_N',

t.NPR_MO as 'NRP_MO',

t.EXTR as 'EXTR',

t.LPU as 'LPU',

t.LPU_1 as 'LPU_1',

t.PODR as 'PODR',

t.PROFIL as 'PROFIL',

-- t.PROFIL_BED as 'PROFIL_BED',

t.DET as 'DET',

t.NHISTORY as 'NHISTORY',

t.DATE_1 as 'DATE_1',

t.DATE_2 as 'DATE_2',

---DATEDIFF(DD, t.DATE_1, t.DATE_2 ) AS 'ED_COL',

t.DS0 as 'DS0',

t.DS1 as 'DS1',

--DS2 as 'DS2'????

--DS3 as 'DS3'????

--us.DS as 'DS',

--VNOV_M as 'VNOV_M',

--CODE_MES1 as 'CODE_MES1',

--CODE_MES2 as 'CODE_MES2',

t.RSLT as 'RSLT',

t.ISHOD as 'ISHOD',

us.PRVS as 'PRVS',

t.VERS_SPEC as 'VERS_SPEC',

t.IDDOKT as 'IDDOKT',

-- t.OS_SLUCH as 'OS_SLUCH',

t.IDSP as 'IDSP',

t.ED_COL as 'ED_COL',

t.TARIF as 'TARIF',

t.SUMV as 'SUMV',

t.OPLATA as 'OPLATA',

t.SUMP as 'SUMP',

t.SANK_IT as 'SANK_IT',

---SANK

(select

s.S_CODE as 'S_CODE',

s.S_SUM as 'S_SUM',

s.S_TIP as 'S_TIP',

s.S_OSN as 'S_OSN',

s.S_COM as 'S_COM',

s.S_IST as 'S_IST'

FOR

XML PATH('SANK'),

TYPE),

--USL

(select

us.IDSERV as 'IDSERV',

us.LPU as 'LPU',

us.LPU_1 as 'LPU_1',

us.PODR as 'PODR',

us.PROFIL as 'PROFIL',

--us.PROFIL_BED as 'PROFIL_BED',

us.VID_VME as 'VID_VME',

us.DET as 'DET',

--us.DATA_IN as 'DATA_IN',

--us.DATA_OUT as 'DATA_OUT',

us.DS as 'DS',

us.CODE_USL as 'CODE_USL',

us.KOL_USL as 'KOL_USL',

--us.OPERATION as 'OPERATION',

--us.TOOTH as 'TOOTH',

us.TARIF as 'TARIF',

us.SUMV_USL as 'SUMV_USL',

us.PRVS as 'PRVS',

us.CODE_MD as 'CODE_MD',

us.COMENTU as 'COMENTU'

FOR

XML PATH('USL'),

TYPE)

FOR

XML PATH('SLUCH'),

TYPE) FOR XML PATH('ZAP'), TYPE)

from TFOMSRK_1026.dbo.RSTR_OMS_XXX_SLUCH t

left join TFOMSRK_1026.dbo.RSTR_OMS_OPL_SCHET o on o.CODE=t.CODE

left join TFOMSRK_1026.dbo.RSTR_OMS_XXX_SANK s on s.CODE=o.CODE

left join TFOMSRK_1026.dbo.RSTR_OMS_XXX_ZAP z on z.CODE=o.CODE and t.N_ZAP=z.N_ZAP

left join TFOMSRK_1026.dbo.RSTR_OMS_XXX_PAC p on p.CODE=o.CODE and p.ID_PAC=z.ID_PAC

left join TFOMSRK_1026.dbo.RSTR_OMS_XXX_USL us on (us.IDCASE=t.IDCASE and us.CODE=o.CODE and us.N_ZAP=z.N_ZAP and t.LPU=us.LPU)

--inner join TEMP.dbo.SEVAST s on rtrim(s.FAM)=Upper(rtrim(p.FAM)) and rtrim(s.IM)=Upper(rtrim(p.IM)) and rtrim(s.OT)=Upper(rtrim(p.OT)) and s.dr=p.dr

LEFT JOIN TFOMSRK_1026.DBO.NSI_V002 V02 ON V02.ID = t.PROFIL

LEFT JOIN TFOMSRK_1026.DBO.NSI_V006 V06 ON V06.ID = t.USL_OK

LEFT JOIN TFOMSRK_1026.DBO.NSI_V009 V09 ON V09.ID = t.RSLT AND V09.USL_OK = t.USL_OK

--LEFT JOIN TFOMSRK_1026.dbo.NSI_LPUP p1 on p1.CodeMO=t.LPU

where o.CODE in ('8791') and o.MONTH BETWEEN 1 AND 12 and t.USL_OK in (1,2) and o.CODE_MO is not null

order by o.MONTH, z.SMO,o.DSCHET

FOR XML PATH(''),

ROOT ('ZL_LIST')

не выходит так как надо,
у меня <ZL_LIST>
  <ZGLV>...  </ZGLV>
  <SCHET>...</SCHET>
  <ZAP>...</ZAP>
<ZGLV>...  </ZGLV>
  <SCHET>...</SCHET>
  <ZAP>...</ZAP>
<ZGLV>...  </ZGLV>
  <SCHET>...</SCHET>
  <ZAP>....</ZAP>
</ZL_LIST>

а должно быть вот так
<ZL_LIST>
  <ZGLV>...  </ZGLV>
  <SCHET>...</SCHET>
  <ZAP>...</ZAP>
<ZAP>...</ZAP>
  <ZAP>....</ZAP>
</ZL_LIST>

что не так в моем запросе????

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| XML, XSL \| Следующая тема »