从XML查询数据
汝弘深
问题内容：
问题答案：
我正在尝试使用sql语句从XML文档中获取特定数据。该文件如下：
<?xml version="1.0" encoding="Shift-JIS" ?>
<Ophthalmology xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:nsCommon="http://www.joia.or.jp/standardized/namespaces/Common" xmlns:nsREF="http://www.joia.or.jp/standardized/namespaces/REF" xsi:schemaLocation="http://www.joia.or.jp/standardized/namespaces/Common Common_schema.xsd http://www.joia.or.jp/standardized/namespaces/REF REF_schema.xsd">
<nsCommon:Common>
<nsCommon:Company>TOPCON</nsCommon:Company>
<nsCommon:ModelName>KR-800</nsCommon:ModelName>
<nsCommon:MachineNo>01</nsCommon:MachineNo>
<nsCommon:ROMVersion>1.05.08</nsCommon:ROMVersion>
<nsCommon:Version>1.2</nsCommon:Version>
<nsCommon:Date>2016-09-17</nsCommon:Date>
<nsCommon:Time>01:17:35</nsCommon:Time>
<nsCommon:Patient>
<nsCommon:No.>0006</nsCommon:No.>
<nsCommon:ID>0006</nsCommon:ID>
<nsCommon:FirstName></nsCommon:FirstName>
<nsCommon:MiddleName></nsCommon:MiddleName>
<nsCommon:LastName></nsCommon:LastName>
<nsCommon:Sex></nsCommon:Sex>
<nsCommon:Age></nsCommon:Age>
<nsCommon:DOB></nsCommon:DOB>
<nsCommon:NameJ1></nsCommon:NameJ1>
<nsCommon:NameJ2></nsCommon:NameJ2>
</nsCommon:Patient>
<nsCommon:Operator>
<nsCommon:No.></nsCommon:No.>
<nsCommon:ID></nsCommon:ID>
</nsCommon:Operator>
</nsCommon:Common>
<nsREF:Measure type="REF">
<nsREF:VD unit="mm">13.75</nsREF:VD>
<nsREF:DiopterStep unit="D">0.25</nsREF:DiopterStep>
<nsREF:AxisStep unit="deg">1</nsREF:AxisStep>
<nsREF:CylinderMode>-</nsREF:CylinderMode>
<nsREF:REF>
<nsREF:R>
<nsREF:List No="1">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-1.25</nsREF:Cylinder>
<nsREF:Axis unit="deg">178</nsREF:Axis>
<nsREF:SE unit="D">-0.25</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:List No="2">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-1.00</nsREF:Cylinder>
<nsREF:Axis unit="deg">177</nsREF:Axis>
<nsREF:SE unit="D">0.00</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:Median>
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-1.00</nsREF:Cylinder>
<nsREF:Axis unit="deg">177</nsREF:Axis>
<nsREF:SE unit="D">0.00</nsREF:SE>
</nsREF:Median>
</nsREF:R>
<nsREF:L>
<nsREF:List No="1">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-0.50</nsREF:Cylinder>
<nsREF:Axis unit="deg">179</nsREF:Axis>
<nsREF:SE unit="D">0.25</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:List No="2">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-0.50</nsREF:Cylinder>
<nsREF:Axis unit="deg">179</nsREF:Axis>
<nsREF:SE unit="D">0.25</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:List No="3">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-0.50</nsREF:Cylinder>
<nsREF:Axis unit="deg">179</nsREF:Axis>
<nsREF:SE unit="D">0.25</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:Median>
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-0.50</nsREF:Cylinder>
<nsREF:Axis unit="deg">179</nsREF:Axis>
<nsREF:SE unit="D">0.25</nsREF:SE>
</nsREF:Median>
</nsREF:L>
</nsREF:REF>
<nsREF:PD>
<nsREF:WorkingDistance unit="cm"></nsREF:WorkingDistance>
<nsREF:Distance unit="mm">68.50</nsREF:Distance>
<nsREF:Near unit="mm">68.50</nsREF:Near>
</nsREF:PD>
</nsREF:Measure>
</Ophthalmology>
我输入了sql查询以获取球体中值，如下所示：
SELECT RSphere
FROM XMLTABLE (
'/nsREF:Measure/nsREF:REF/nsREF:R/nsREF:Median/nsREF:Sphere'
PASSING XMLTYPE (BFILENAME ('MY_DIR', 'aaa.xml'),
NLS_CHARSET_ID ('Shift-JIS'))
COLUMNS RSphere VARCHAR2 (255) PATH 'text()')
但是我出错了
[错误]执行（4：17）：ORA-19228：XPST0008-未声明的标识符：前缀’nsREF’本地名称’nsREF：Measure’
您能帮我获得正确的价格吗
问题答案：
Oracle安装程序
CREATE TABLE data ( xml XMLType );
DECLARE
xmlString CLOB := '<?xml version="1.0" encoding="Shift-JIS" ?>
<Ophthalmology xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:nsCommon="http://www.joia.or.jp/standardized/namespaces/Common" xmlns:nsREF="http://www.joia.or.jp/standardized/namespaces/REF" xsi:schemaLocation="http://www.joia.or.jp/standardized/namespaces/Common Common_schema.xsd http://www.joia.or.jp/standardized/namespaces/REF REF_schema.xsd">
<nsCommon:Common>
<nsCommon:Company>TOPCON</nsCommon:Company>
<nsCommon:ModelName>KR-800</nsCommon:ModelName>
<nsCommon:MachineNo>01</nsCommon:MachineNo>
<nsCommon:ROMVersion>1.05.08</nsCommon:ROMVersion>
<nsCommon:Version>1.2</nsCommon:Version>
<nsCommon:Date>2016-09-17</nsCommon:Date>
<nsCommon:Time>01:17:35</nsCommon:Time>
<nsCommon:Patient>
<nsCommon:No.>0006</nsCommon:No.>
<nsCommon:ID>0006</nsCommon:ID>
<nsCommon:FirstName></nsCommon:FirstName>
<nsCommon:MiddleName></nsCommon:MiddleName>
<nsCommon:LastName></nsCommon:LastName>
<nsCommon:Sex></nsCommon:Sex>
<nsCommon:Age></nsCommon:Age>
<nsCommon:DOB></nsCommon:DOB>
<nsCommon:NameJ1></nsCommon:NameJ1>
<nsCommon:NameJ2></nsCommon:NameJ2>
</nsCommon:Patient>
<nsCommon:Operator>
<nsCommon:No.></nsCommon:No.>
<nsCommon:ID></nsCommon:ID>
</nsCommon:Operator>
</nsCommon:Common>
<nsREF:Measure type="REF">
<nsREF:VD unit="mm">13.75</nsREF:VD>
<nsREF:DiopterStep unit="D">0.25</nsREF:DiopterStep>
<nsREF:AxisStep unit="deg">1</nsREF:AxisStep>
<nsREF:CylinderMode>-</nsREF:CylinderMode>
<nsREF:REF>
<nsREF:R>
<nsREF:List No="1">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-1.25</nsREF:Cylinder>
<nsREF:Axis unit="deg">178</nsREF:Axis>
<nsREF:SE unit="D">-0.25</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:List No="2">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-1.00</nsREF:Cylinder>
<nsREF:Axis unit="deg">177</nsREF:Axis>
<nsREF:SE unit="D">0.00</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:Median>
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-1.00</nsREF:Cylinder>
<nsREF:Axis unit="deg">177</nsREF:Axis>
<nsREF:SE unit="D">0.00</nsREF:SE>
</nsREF:Median>
</nsREF:R>
<nsREF:L>
<nsREF:List No="1">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-0.50</nsREF:Cylinder>
<nsREF:Axis unit="deg">179</nsREF:Axis>
<nsREF:SE unit="D">0.25</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:List No="2">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-0.50</nsREF:Cylinder>
<nsREF:Axis unit="deg">179</nsREF:Axis>
<nsREF:SE unit="D">0.25</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:List No="3">
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-0.50</nsREF:Cylinder>
<nsREF:Axis unit="deg">179</nsREF:Axis>
<nsREF:SE unit="D">0.25</nsREF:SE>
<nsREF:CataractMode></nsREF:CataractMode>
<nsREF:IOLMode></nsREF:IOLMode>
<nsREF:ConfidenceIndex></nsREF:ConfidenceIndex>
</nsREF:List>
<nsREF:Median>
<nsREF:Sphere unit="D">0.50</nsREF:Sphere>
<nsREF:Cylinder unit="D">-0.50</nsREF:Cylinder>
<nsREF:Axis unit="deg">179</nsREF:Axis>
<nsREF:SE unit="D">0.25</nsREF:SE>
</nsREF:Median>
</nsREF:L>
</nsREF:REF>
<nsREF:PD>
<nsREF:WorkingDistance unit="cm"></nsREF:WorkingDistance>
<nsREF:Distance unit="mm">68.50</nsREF:Distance>
<nsREF:Near unit="mm">68.50</nsREF:Near>
</nsREF:PD>
</nsREF:Measure>
</Ophthalmology>';
BEGIN
INSERT INTO DATA ( xml ) VALUES ( XMLType( xmlString ) );
END;
/
COMMIT;
查询 ：
SELECT x.*
FROM Data d,
XMLTABLE (
XMLNAMESPACES(
'http://www.joia.or.jp/standardized/namespaces/Common' AS "nsCommon",
'http://www.joia.or.jp/standardized/namespaces/REF' AS "nsREF"
),
'//Ophthalmology/nsREF:Measure/nsREF:REF/nsREF:R/nsREF:Median'
PASSING d.xml
COLUMNS RSphere NUMBER(5,2) PATH '//nsREF:Sphere',
RCylinder NUMBER(5,2) PATH '//nsREF:Cylinder',
RAxis NUMBER(5,2) PATH '//nsREF:Axis',
RSE NUMBER(5,2) PATH '//nsREF:SE'
) x;
输出 ：
RSPHERE RCYLINDER RAXIS RSE
---------- ---------- ---------- ----------
.5 -1 177 0
类似资料：
-
问题内容： 我在MySQL的ExtractValue函数方面有点问题。 这是我的示例XML： 这是我当前的查询： 结果如下： 这是我想要的： 有人可以帮我实现这一点..谢谢。 问题答案： 在这里回答了这个问题： 在MySQL中解析XML字符串 如果将子级更改为“ B”，则该文章中的解决方案应该起作用：
-
我正试图从firebase数据库中获取项目列表。。。但我很难得到它们。我的代码大致如下： 应该返回我的项目的方法如下所示： GetItems（）总是返回一个空列表，这有点道理，因为在我返回这个列表之前，没有什么可以触发onDataChange事件，所以我的问题是。。。如何使用此方法从DB返回项目列表？
-
例子 (Demo Example) 这是我们需要解析的输入xml文件 - <?xml version = "1.0"?> <class> <student rollno = "393"> <firstname>dinkar</firstname> <lastname>kad</lastname> <nickname>dinkar</nickname>
-
例子 (Demo Example) 这是我们需要查询的输入文本文件 - <?xml version = "1.0"?> <class> <student rollno = "393"> <firstname>dinkar</firstname> <lastname>kad</lastname> <nickname>dinkar</nickname>
-
例子 (Demo Example) 这是我们需要解析的输入xml文件 - <?xml version = "1.0"?> <class> <student rollno = "393"> <firstname>dinkar</firstname> <lastname>kad</lastname> <nickname>dinkar</nickname>
-
例子 (Demo Example) 这是我们需要查询的输入xml文件 - <?xml version = "1.0"?> <cars> <supercars company = "Ferrari"> <carname type = "formula one">Ferarri 101</carname> <carname type = "sports car">Fera