我有以下代码将xml导入SQL
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT rid, uid
FROM OPENXML(@hDoc, '/PportTimetable/Journey')
WITH
(
rid [varchar](50) '@rid',
uid [varchar](100) '@uid'
)
EXEC sp_xml_removedocument @hDoc
GO
我可以使代码正常工作,但是只有当它不包含xmlns信息时(如下所示),这是为什么?
xmlns:xsd =“ http://www.w3.org/2001/XMLSchema”
xmlns:xsi =“ http://www.w3.org/2001/XMLSchema-instance”
xmlns =“ http://www.thalesgroup.com/rtti/XmlTimetable/v8”
XML标头
<PportTimetable xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" timetableID="20161018020822" xmlns="http://www.thalesgroup.com/rtti/XmlTimetable/v8">
<Journey rid="201610188012733" uid="P12733" trainId="2J27" ssd="2016-10-18" toc="AW">
</Journey>
</PportTimetable>
我建议OPENXML
完全跳过这些内容,并在SQL Server中使用内置的本机XQuery支持:
declare @input XML = '<PportTimetable xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" timetableID="20161018020822" xmlns="http://www.thalesgroup.com/rtti/XmlTimetable/v8">
<Journey rid="201610188012733" uid="P12733" trainId="2J27" ssd="2016-10-18" toc="AW">
</Journey>
</PportTimetable>'
-- define your XML namespaces - here, there's only a single "default" namespace
;WITH XMLNAMESPACES(DEFAULT 'http://www.thalesgroup.com/rtti/XmlTimetable/v8')
SELECT
RID = XC.value('@rid', 'varchar(50)'),
UID = XC.value('@uid', 'varchar(20)'),
TrainId = XC.value('@trainId', 'varchar(25)'),
SSD = XC.value('@ssd', 'varchar(25)'),
TOC = XC.value('@toc', 'varchar(20)')
FROM
@input.nodes('/PportTimetable/Journey') AS XT(XC)
使用XQuery.nodes()
函数将XML“切碎”到XML片段的“内联”表中(<Journey>
在此示例中,每个节点一个),然后使用该.value()
函数从这些XML片段中逐个抓取各个元素和属性。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句