sql-server - 使用 T-SQL 解析 XML
问题描述
我正在尝试在 T-SQL 中解析 XML,但不明白如何编写我的代码。
我的 XML 输出:
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="https://api.quinyx.com/soap/FlexForce">
<SOAP-ENV:Body>
<ns1:wsdlGetTimePunchesResponse xmlns:ns1="uri:FlexForce">
<return xsi:type="tns:GetTimePunchesResponse">
<timepunches xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="tns:TimePunch[1868]">
<item xsi:type="tns:TimePunch">
<id xsi:type="xsd:int">134054629</id>
<employeeName xsi:type="xsd:string">Test, Person</employeeName>
<restId xsi:type="xsd:int">1111</restId>
<badgeNo xsi:type="xsd:string">5000</badgeNo>
<socsecNo xsi:type="xsd:string" />
<cardNo xsi:type="xsd:string">5000</cardNo>
<punchIn xsi:type="xsd:dateTime">2019-01-14T08:00:00+01:00</punchIn>
<punchOut xsi:type="xsd:dateTime">2019-01-14T17:00:00+01:00</punchOut>
<hours xsi:type="xsd:decimal">8</hours>
<approvedByEmployee xsi:type="xsd:boolean">true</approvedByEmployee>
<approvedByManager xsi:type="xsd:boolean">true</approvedByManager>
<approvedByCustomer xsi:type="xsd:boolean">false</approvedByCustomer>
<costCentre xsi:type="xsd:string">8002</costCentre>
<projectNo xsi:type="xsd:string" />
<accountNo xsi:type="xsd:string" />
<agrmntAdditionalField1 xsi:type="xsd:string" />
<agrmntAdditionalField2 xsi:type="xsd:string" />
<agrmntAdditionalField3 xsi:type="xsd:string" />
<agrmntAdditionalField4 xsi:type="xsd:string" />
<agrmntAdditionalField5 xsi:type="xsd:string" />
<externalInfo1 xsi:type="xsd:string" />
<externalInfo2 xsi:type="xsd:string" />
<externalInfo3 xsi:type="xsd:string" />
<scheduleId xsi:type="xsd:int">2000000</scheduleId>
<transferredToPayroll xsi:type="xsd:boolean">true</transferredToPayroll>
<deleted xsi:type="xsd:boolean">false</deleted>
<productiveTime xsi:type="xsd:boolean">true</productiveTime>
<isOpen xsi:type="xsd:boolean">false</isOpen>
<comment xsi:type="xsd:string" />
<managerComment xsi:type="xsd:string" />
<employeeId xsi:type="xsd:int">1000000</employeeId>
<ts xsi:type="xsd:dateTime">2019-09-07T09:17:48+02:00</ts>
</item>
</timepunches>
<validationErrors xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="xsd:string[0]" />
</return>
XML 存储在我的数据库表中的单个列中。
我尝试了多种方法来获取任何数据,但最终都没有显示任何数据。
甚至不知道从哪里开始说实话。
它是一个 SQL Server 数据库。
这是现有的查询:
select cast( replace(XMLData, '<?xml version="1.0" encoding="UTF-8"?>','') as xml) as XMLData into #temp from [Quniyx].[xmltest] select XMLData.value('return[1]/TimePunch[1]/item[1]/id[1]','int') from #temp
解决方案
提供的 XML 格式不正确。我不得不调整它。此外,XML 有多个名称空间。其余的都是微不足道的。
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xml_data XML);
INSERT INTO @tbl
VALUES
('<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:tns="https://api.quinyx.com/soap/FlexForce">
<SOAP-ENV:Body>
<ns1:wsdlGetTimePunchesResponse xmlns:ns1="uri:FlexForce">
<return xsi:type="tns:GetTimePunchesResponse">
<timepunches xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="tns:TimePunch[1868]">
<item xsi:type="tns:TimePunch">
<id xsi:type="xsd:int">134054629</id>
<employeeName xsi:type="xsd:string">Test, Person</employeeName>
<restId xsi:type="xsd:int">1111</restId>
<badgeNo xsi:type="xsd:string">5000</badgeNo>
<socsecNo xsi:type="xsd:string"/>
<cardNo xsi:type="xsd:string">5000</cardNo>
<punchIn xsi:type="xsd:dateTime">2019-01-14T08:00:00+01:00</punchIn>
<punchOut xsi:type="xsd:dateTime">2019-01-14T17:00:00+01:00</punchOut>
<hours xsi:type="xsd:decimal">8</hours>
<approvedByEmployee xsi:type="xsd:boolean">true</approvedByEmployee>
<approvedByManager xsi:type="xsd:boolean">true</approvedByManager>
<approvedByCustomer xsi:type="xsd:boolean">false</approvedByCustomer>
<costCentre xsi:type="xsd:string">8002</costCentre>
<projectNo xsi:type="xsd:string"/>
<accountNo xsi:type="xsd:string"/>
<agrmntAdditionalField1 xsi:type="xsd:string"/>
<agrmntAdditionalField2 xsi:type="xsd:string"/>
<agrmntAdditionalField3 xsi:type="xsd:string"/>
<agrmntAdditionalField4 xsi:type="xsd:string"/>
<agrmntAdditionalField5 xsi:type="xsd:string"/>
<externalInfo1 xsi:type="xsd:string"/>
<externalInfo2 xsi:type="xsd:string"/>
<externalInfo3 xsi:type="xsd:string"/>
<scheduleId xsi:type="xsd:int">2000000</scheduleId>
<transferredToPayroll xsi:type="xsd:boolean">true</transferredToPayroll>
<deleted xsi:type="xsd:boolean">false</deleted>
<productiveTime xsi:type="xsd:boolean">true</productiveTime>
<isOpen xsi:type="xsd:boolean">false</isOpen>
<comment xsi:type="xsd:string"/>
<managerComment xsi:type="xsd:string"/>
<employeeId xsi:type="xsd:int">1000000</employeeId>
<ts xsi:type="xsd:dateTime">2019-09-07T09:17:48+02:00</ts>
</item>
</timepunches>
<validationErrors xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="xsd:string[0]"/>
</return>
</ns1:wsdlGetTimePunchesResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>');
-- DDL and sample data population, end
;WITH XMLNAMESPACES ('uri:FlexForce' as ns1, 'http://schemas.xmlsoap.org/soap/envelope/' AS [SOAP-ENV])
SELECT ID
, col.value('(id/text())[1]','INT') AS id
, col.value('(employeeName/text())[1]','VARCHAR(200)') AS employeeName
, col.value('(restId/text())[1]','VARCHAR(200)') AS restId
-- add the rest of the elements here
FROM @tbl tbl
CROSS APPLY tbl.xml_data.nodes('/SOAP-ENV:Envelope/SOAP-ENV:Body/ns1:wsdlGetTimePunchesResponse/return/timepunches/item') AS tab(col)
输出
+----+-----------+--------------+--------+
| ID | id | employeeName | restId |
+----+-----------+--------------+--------+
| 1 | 134054629 | Test, Person | 1111 |
+----+-----------+--------------+--------+
推荐阅读
- elasticsearch - 如何比较自定义观察者中的信息 - Elasticsearch?
- java - Kotlin/LayoutInflater 与 viewBinding
- javascript - React 路由器在多项目 React 应用程序中处理未定义的路由
- javascript - npm WARN 使用 --force 禁用推荐的保护
- c - 分配结构值时 const 关键字有什么用?
- r - 对具有相似字符串的行求和
- reactjs - 如何将用户输入设置为大写
- wordpress - 海洋 wp 中头部站点上方的内容
- vim-plugin - neovim :PlugUpdate 对所有插件产生权限被拒绝
- c - 在内核中禁用 TCP 堆栈并创建用户空间 TCP 堆栈——称其为服务器——可能是什么挑战