天道酬勤,学无止境

xmltype

Parsing XML with unknown namespaces in Oracle SQL

I'm having trouble with Oracle SQL and XMLs. I'll be getting loads of clobs of well-formed XML data from an external system to parse, interpret and fill some tables with. I wrote a solution using XMLTable, which is laid out in a view on the table with the xml clob column in question and some audit info and stuff (I'd like to keep it this way). NAMESPACES are giving me nightmares. Apparently I can't put them in the xmlnamespaces clause, because I can never know what they are going to be. Preposterous! Delivered items of the same type could have different namespaces at different points in time

2021-11-28 08:52:02    分类:问答    sql   oracle   parsing   namespaces   xmltype

Mapping Oracle XMLType on JPA (EclipseLink)

We have a project with some special requirements, one of wich is getting data from a XMLType database column from an Oracle 10g database. We have found an easy solution using JDBC, but it would drive the application a little messy, as all the data access is being done through JPA (the implementation used is EclipseLink). We have done some research, and have found some solutions, as using Converters and other auxiliar types, but the implementations seemed a little complicated. So, my question is: Could you recommend me an easy way to map an XMLType data column to a Java Object type, using JPA

2021-11-22 11:11:29    分类:问答    java   jpa   eclipselink   xmltype

Oracle-XMLTYPE : How to update a value

I have an Oracle table with a xmltype column that stores XML in the following format <?xml version="1.0" encoding="WINDOWS-1252"?> <View> <ReportValues> <SalaryValue variable="HR" value="999"/> <SalaryValue variable="floor" value="20"/> </ReportValues> </View> I would like to know how to update the value from 999 to 666 for variable "HR" and also the variable value from "floor" to "SALES"

2021-11-20 13:17:39    分类:问答    sql   oracle   xmltype

C# 在 XmlType() 属性中使用特殊字符转义 XmlType 名称(C# Escaping XmlType Name with special char within XmlType() Attribute)

问题 我需要在不使用 WSDL 的情况下实现和 SOAP 服务(因为它已损坏并且不会被修复)。 现在我停留在需要为某些类(基类和派生类)设置类型属性的地方 通常会像这样装饰我的 DTO: 基类: [XmlInclude(typeof(Person))] [XmlType("Partner", Namespace = "http://....")] public abstract class Partner {...} 和 DerivedOne: [XmlType("Person")] public class Person : Partner {...} 结果如下所示: <tar:Partner xsi:type="Person"> 但现在 API 文档告诉我创建一个请求,如: <tar:Partner xsi:type="par:CT_Person"> (在 Fiddler 中测试并有效)如何创建带有 NameSpace Prefix (par) 的 typeName 集或将名称设置为: [XmlType("par:CT_Person")] ? 停用(覆盖)转义功能对我来说是没有选择的。 提前致谢。 回答1 经过一整天的测试和尝试......写完我的问题后,我自己得到了答案。 当我在我的基类中设置命名空间时,我可以使用不同的 URL 在派生的命名空间中设置一个命名空间。 比它有效

2021-11-11 04:46:35    分类:技术分享    c#   xml   xmlserializer   xmltype

C# Escaping XmlType Name with special char within XmlType() Attribute

i need to implement and SOAP Service without using the WSDL (because its broken and will not be fixed). Now im stuck at a point where i need to set type attributes for some classes (base classes and derived one) normaly ill decorate my DTOs like this: BaseClass: [XmlInclude(typeof(Person))] [XmlType("Partner", Namespace = "http://....")] public abstract class Partner {...} and the DerivedOne: [XmlType("Person")] public class Person : Partner {...} the result looks like this: <tar:Partner xsi:type="Person"> but now the API Document tells me to create a request like: <tar:Partner xsi:type="par

2021-11-09 01:09:35    分类:问答    c#   xml   xmlserializer   xmltype

加载 XML 数据时出错,说我的控制文件“引用了一个不存在的字段”(Loading XML data gets error saying my control file "references a non existent field")

问题 我使用 SQL*Loader 将 XML 文档从文本文件加载到 Oracle 表中时出错。 我创建了一个 XML 表: CREATE TABLE TEST_XML OF XMLTYPE XMLTYPE STORE AS SECUREFILE BINARY XML; 我有一个文件test_file.xml : <ROWSET> <ROW> <ID>1</ID> <TEXT>This is some text</TEXT> </ROW> <ROW> <ID>2</ID> <TEXT>This is some more text</TEXT> </ROW> <ROW> <ID>3</ID> <TEXT>This is some other text</TEXT> </ROW> <ROW> <ID>4</ID> <TEXT>This is also some text</TEXT> </ROW> </ROWSET> 我创建了一个控制文件test_loading.ctl : LOAD DATA INFILE "test_file.xml" append INTO TABLE TEST_XML xmltype(XMLDATA) ( XMLDATA LOBFILE("test_file.xml") TERMINATED BY EOF ) 当我使用该控制文件运行 SQL*Loader 时:

2021-10-19 02:09:33    分类:技术分享    oracle   sql-loader   xmltype

Loading XML data gets error saying my control file "references a non existent field"

I am getting an error loading an XML document from a text file into an Oracle table using SQL*Loader. I have created an XML table: CREATE TABLE TEST_XML OF XMLTYPE XMLTYPE STORE AS SECUREFILE BINARY XML; And I have a file test_file.xml: <ROWSET> <ROW> <ID>1</ID> <TEXT>This is some text</TEXT> </ROW> <ROW> <ID>2</ID> <TEXT>This is some more text</TEXT> </ROW> <ROW> <ID>3</ID> <TEXT>This is some other text</TEXT> </ROW> <ROW> <ID>4</ID> <TEXT>This is also some text</TEXT> </ROW> </ROWSET> I have created a control file test_loading.ctl: LOAD DATA INFILE "test_file.xml" append INTO TABLE TEST_XML

2021-10-18 22:01:27    分类:问答    oracle   sql-loader   xmltype

如何在 Oracle PLSQL 中获取自闭合 xml 标记?(How to get a self-closing xml tag in Oracle PLSQL?)

问题 请求的输出应如下所示: <Consignment id="123" date="2017-06-08"> <Box id="321" /> </Consignment> 其中<box>标签应该像上面一样自动关闭。 我正在使用以下代码: SELECT XMLELEMENT( "Consignment", XMLATTRIBUTES('123' AS "id",sysdate AS "date" ), XMLELEMENT( "Box", xmlattributes( '321' as "id" )) ).getstringval() as xxx FROM DUAL; 但它总是返回以下结果(其中标签<box>有一个单独的结束标签</box> ): <Consignment id="123" date="2017-06-08"> <Box id="321"></Box> </Consignment> 如何让上面的<box>标签自闭? 回答1 如果您只需要使用“box”来执行此操作,那么您可以使用: SELECT REPLACE(XMLELEMENT( "Consignment", XMLATTRIBUTES('123' AS "id",sysdate AS "date" ), XMLELEMENT( "Box", xmlattributes( '321' as "id" )) )

2021-10-15 16:58:33    分类:技术分享    xml   oracle   xmltype   xml-generation

Split up query results after loading XML with fn:string-join in an Oracle table

I've loaded a few XML files into an Oracle database (11gr2 on Linux x64) and I had to use a fn:string-join to succesfully import the xml files which contain multiple records. After the data has landed/has been imported to an XMLTYPE table using SQL*LOADER, i've issued a query which transports the XML data from the XMLTYPE table to a regular table, lets call that one Table_A This table now contains all fields of the imported records (2 records in this case) together in the target fields, so for instance. The xml file contains an entry for computer_brand and the first record has the value 'Dell'

2021-10-11 10:31:11    分类:问答    oracle   xmltype

将 XML 存储到 Postgres(Storing XML into Postgres)

问题 我有一个需要存储在 SQL db (Postgres) 中的 XML 文档。 我已经看到这是如何完成的,但我有一个问题:我是否只创建一个带有 xml 字段的表并将整个文档放在那里? 这是一个关于电影等(电影、演员...)的文档,其中包含稍后要检索的信息。 我从来没有在数据库中使用过 XML,所以我有点困惑。 这是我的 XML 的示例: <?xml version="1.0" encoding="UTF-8"?> <cinema xmlns="movies" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="movies file:/C:/Users/Fabio/git/LAPD/movies.xsd"> <persons> <person id="P1"> <name>Samuel L. Jackson</name> <birth>1948-12-21</birth> </person> <person id="P2"> <name>Leonardo Di Caprio</name> <birth>1974-11-11</birth> </person> <person id="P3"> <name>Quentin Tarantino</name> <birth>1963-03

2021-10-06 20:09:53    分类:技术分享    sql   xml   database   postgresql   xmltype