我有以下类型的树数据:1个主题,2个护照,2个地址,这是一个实体。如何使用嵌套的xml标签序列化为xml树数据?数据类型:
当我使用XMLAGG在Clob中构建xml时,我得到了带有重复标签的结构,例如:
<xml>
<person>
<title></title>
<document1></document1>
<document2></document2>
<adress1></adress1>
<adress2></adress2>
<adress1></adress1>
<phone1></phone1>
<phone2></phone2>
<phone3></phone3>
<phone1></phone1>
<phone2></phone2>
<phone3></phone3>
</person>
</xml>
这是我对CLOB中的构建XML的查询:
---XMLPERSOM
SELECT XMLELEMENT ("Person",
XMLELEMENT ("ReferenceCode", SBS.ReferenceCode),
XMLELEMENT ("LastName", SBS.LastName),
XMLELEMENT ("FirstName", SBS.FirstName),
XMLELEMENT ("MiddleName", SBS.MiddleName),
XMLELEMENT ("BirthDate", SBS.BirthDate),
XMLELEMENT ("BirthPlace", SBS.BirthPlace),
XMLELEMENT ("sourceCode", SBS.sourceCode),
XMLELEMENT ("GroupCode", SBS.GroupCode),
--XMLDOCUMENTS
XMLELEMENT ("Documents",
XMLAGG(
XMLELEMENT ("Document",
XMLELEMENT ("Type", DCS.Type),
XMLELEMENT ("Series", DCS.Series),
XMLELEMENT ("Number", DCS.NumberID),
XMLELEMENT ("IssueDate", DCS.IssueDate),
XMLELEMENT ("IssueAuthority", DCS.IssueAuthority)))),
XMLELEMENT ("Addresses",
XMLAGG(
XMLELEMENT ("Address",
XMLELEMENT ("Type", ADDS.Type),
XMLELEMENT ("Location", ADDS.Location),
XMLELEMENT ("Street", ADDS.Street),
XMLELEMENT ("PostalCode", ADDS.PostalCode),
XMLELEMENT ("Country", ADDS.RU),
XMLELEMENT ("Region", ADDS.Region)))),
)
FROM SUBJECT_SEGMENT SBS
LEFT JOIN DOCUMENT_SEGMENT DCS ON SBS.ID = DCS.SUBJECT_ID
LEFT JOIN ADDRESS_SEGMENT ADDS ON SBS.ID = ADDS.SUBJECT_ID
GROUP BY SBS.ReferenceCode,
SBS.LastName,
SBS.FirstName,
SBS.MiddleName,
SBS.BirthDate,
SBS.BirthPlace,
SBS.sourceCode,
SBS.GroupCode
结果,我有重复的标签或重复的Clob xml。我需要一个Clob,并且没有重复的标签。感谢帮助!
如果某人有三个文档和两个地址,则查询将为此人生成6行。这就是价值重复的原因。您可以解决此问题,例如,使用文档,地址和电话的相关子查询,例如:
SELECT XMLELEMENT ("Person",
XMLELEMENT ("ReferenceCode", ReferenceCode),
XMLELEMENT ("LastName", LastName),
XMLELEMENT ("Documents",
(select XMLAGG(XMLELEMENT ("Document", XMLELEMENT ("Type", Type),
XMLELEMENT ("Number", NumberID)))
from doc where referencecode = sbs.referencecode)),
XMLELEMENT ("Addresses",
(select XMLAGG(XMLELEMENT ("Address", XMLELEMENT ("Street", Street)))
from adr where referencecode = sbs.referencecode)))
FROM SBS
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句