从XML查询数据

小编典典

从XML查询数据

sql

我正在尝试使用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’

您能帮我获得正确的价格吗

阅读 230

2021-05-05

共1个答案

小编典典

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

2021-05-05