Postgres中的OPENXML

本文介绍了Postgres中的OPENXML的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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