Postgres中的OPENXML [英] OPENXML in Postgres

问题描述

SQLServer具有一个非常有用的功能，称为 OPENXML 。它的工作原理是这样的：

SQLServer has a very useful function called OPENXML. It works is used like this:

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID  varchar(10),
      ContactName varchar(20))

结果：

CustomerID ContactName          
---------- -------------------- 
VINET      Paul Henriot
LILAS      Carlos Gonzlez

有人知道Postgres的替代方法吗？

Does someone know of an alternative for Postgres?

推荐答案

我确定可以改进此代码，但这似乎在Postgres中返回与在SQL Server中发布的代码相同的结果。

I'm sure this code can be improved, but this appears to return the same result in Postgres as the code you posted does in SQL Server.

WITH test_xml(data) AS (VALUES
  ('<ROOT>
    <Customer CustomerID="VINET" ContactName="Paul Henriot">
       <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
          <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
          <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
       </Order>
    </Customer>
    <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
       <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
          <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
       </Order>
    </Customer>
    </ROOT>'::XML)
)
SELECT  unnest((xpath('//Customer/@CustomerID', test_xml.data))),
        unnest((xpath('//Customer/@ContactName', test_xml.data)))
FROM test_xml

这篇关于Postgres中的OPENXML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！