在 SQL Server 中使用 OpenXML 从 XML 中提取数据

Question

提问by Rush

I have a xml which I want it to be extracted using OpenXML within SQL Server

我有一个 xml，我希望在 SQL Server 中使用 OpenXML 提取它

Here is the sample XML

这是示例 XML

<row>
  <student_token>7</student_token>
  <student_ssn>552</student_ssn>
  <alternate_id>20</alternate_id>
  <old_ssn xsi:nil="true" />
  <alien_num xsi:nil="true" />
  <last_name>A</last_name>
  <first_name>B</first_name>
  <middle_init xsi:nil="true" />
  <drivers_license_num xsi:nil="true" />
  <gpa_highschool xsi:nil="true" />
  <created_dt>2006-07-13T11:15:08.320</created_dt>
  <created_how>4</created_how>
  <modified_dt>2008-02-14T00:00:00</modified_dt>
  <modified_by>4</modified_by>
  <primary_street2 xsi:nil="true" />
  <primary_street3 xsi:nil="true" />
  <primary_country xsi:nil="true" />
  <email_address xsi:nil="true" />
  <address_start_dt xsi:nil="true" />
  <address_end_dt xsi:nil="true" />
  <entrance_iv_dt xsi:nil="true" />
  <entrance_iv_by xsi:nil="true" />
  <exit_iv_dt>2006-11-02T00:00:00</exit_iv_dt>
  <exit_iv_by>156</exit_iv_by>
  <foreign_address_indicator>N</foreign_address_indicator>
  <foreign_postal_code xsi:nil="true" />
  <pin>J27841</pin>
  <web_id>J08614   </web_id>
  <prior_name xsi:nil="true" />
  <orig_eps xsi:nil="true" />
  <web_role>STU1</web_role>
  <heal_limit_flag>N</heal_limit_flag>
  <email_address_2>[email protected]</email_address_2>
  <cellular_telephone>415</cellular_telephone>
  <alt_loan_debt xsi:nil="true" />
  <web_last_login xsi:nil="true" />
  <foreign_country_code xsi:nil="true" />
  <entrance_iv_dt_grad_plus xsi:nil="true" />
  <entrance_iv_by_grad_plus xsi:nil="true" />
  <failed_logins>0</failed_logins>
  <hispanic xsi:nil="true" />
  <race xsi:nil="true" />
  <primary_phone_number_intl xsi:nil="true" />
  <security_version>0</security_version>
  <failed_challenge_response>0</failed_challenge_response>
  <require_pin_reset xsi:nil="true" />
</row>

The query should extract into 3 fields for each row

查询应为每行提取 3 个字段

FieldName
FieldValue
IsNull

字段名
字段值
一片空白

For example the first row should be

例如第一行应该是

FieldName = student_token - The node name would be the field name
FieldValue = 7
IsNull = false - IsNull is based on the attribute xsi:nil="true"

FieldName = student_token - 节点名称将是字段名称
字段值 = 7
IsNull = false - IsNull 基于属性 xsi:nil="true"

How can I do this? Any help would be appreciated.

我怎样才能做到这一点？任何帮助，将不胜感激。

Thanks

谢谢

Answer 1

回答by Mikael Eriksson

Sample data with namespace added.

添加了命名空间的示例数据。

declare @xml xml 
set @xml = 
'<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <student_token>7</student_token>
  <student_ssn>552</student_ssn>
  <alternate_id>20</alternate_id>
  <old_ssn xsi:nil="true" />
  <alien_num xsi:nil="true" />
  <last_name>A</last_name>
  <first_name>B</first_name>
  <middle_init xsi:nil="true" />
  <drivers_license_num xsi:nil="true" />
  <gpa_highschool xsi:nil="true" />
  <created_dt>2006-07-13T11:15:08.320</created_dt>
  <created_how>4</created_how>
  <modified_dt>2008-02-14T00:00:00</modified_dt>
  <modified_by>4</modified_by>
  <primary_street2 xsi:nil="true" />
  <primary_street3 xsi:nil="true" />
  <primary_country xsi:nil="true" />
  <email_address xsi:nil="true" />
  <address_start_dt xsi:nil="true" />
  <address_end_dt xsi:nil="true" />
  <entrance_iv_dt xsi:nil="true" />
  <entrance_iv_by xsi:nil="true" />
  <exit_iv_dt>2006-11-02T00:00:00</exit_iv_dt>
  <exit_iv_by>156</exit_iv_by>
  <foreign_address_indicator>N</foreign_address_indicator>
  <foreign_postal_code xsi:nil="true" />
  <pin>J27841</pin>
  <web_id>J08614   </web_id>
  <prior_name xsi:nil="true" />
  <orig_eps xsi:nil="true" />
  <web_role>STU1</web_role>
  <heal_limit_flag>N</heal_limit_flag>
  <email_address_2>[email protected]</email_address_2>
  <cellular_telephone>415</cellular_telephone>
  <alt_loan_debt xsi:nil="true" />
  <web_last_login xsi:nil="true" />
  <foreign_country_code xsi:nil="true" />
  <entrance_iv_dt_grad_plus xsi:nil="true" />
  <entrance_iv_by_grad_plus xsi:nil="true" />
  <failed_logins>0</failed_logins>
  <hispanic xsi:nil="true" />
  <race xsi:nil="true" />
  <primary_phone_number_intl xsi:nil="true" />
  <security_version>0</security_version>
  <failed_challenge_response>0</failed_challenge_response>
  <require_pin_reset xsi:nil="true" />
</row>'

Using openxml.

使用openxml.

declare @idoc int
exec sp_xml_preparedocument @idoc out, @xml, '<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'

select FieldName, 
       FieldValue,
       isnull([IsNull], 0)
from openxml(@idoc, '/row/*',1) 
  with (
         FieldName  varchar(50) '@mp:localname',
         FieldValue varchar(50) '.',
         [IsNull]   bit         '@xsi:nil'
       )

exec sp_xml_removedocument @idoc

Using the XML data type:

使用 XML 数据类型：

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select T.N.value('local-name(.)', 'varchar(50)') as FieldName,
       T.N.value('.', 'varchar(50)') as FieldValue,
       isnull(T.N.value('@ns:nil', 'bit'), 0) as [IsNull]
from @xml.nodes('/row/*') as T(N)

Answer 2

回答by marc_s

Not sure if you have that XML as a SQL variable or inside a table - question is very unclear .....

不确定您是否将该 XML 作为 SQL 变量或在表中 - 问题非常不清楚.....

If you have it as an SQL variable, then try something like this (note: you mustdeclare the xsiprefix somehow - otherwise SQL Server's XML processor won't even look at your XML document):

如果你把它作为一个 SQL 变量，那么试试这样的事情（注意：你必须xsi以某种方式声明前缀 - 否则 SQL Server 的 XML 处理器甚至不会查看你的 XML 文档）：

DECLARE @input XML = '<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <student_token>7</student_token>
  <student_ssn>552</student_ssn>
  <alternate_id>20</alternate_id>
  <old_ssn xsi:nil="true" />
  .........
</row>'

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as xsi)
SELECT
FieldName = T.C.value('local-name(.)', 'varchar(50)'),
FieldValue = T.C.value('(.)[1]', 'varchar(500)'),
IsNIL = ISNULL(T.C.value('(@xsi:nil)[1]', 'bit'), 0)
FROM
@Input.nodes('/row/*') AS T(C)

This gives me an output something like:

这给了我一个类似的输出：

FieldName      FieldValue  IsNIL
student_token    7          0
student_ssn      552        0
alternate_id     20         0
old_ssn                     1
.....

Of course, all output is going to be of type varchar(500)now in the FieldValuecolumn....

当然，所有的输出将是类型的varchar(500)目前在FieldValue列....

Updated my answer, based on Mikael Eriksson's answer, to include the IsNILhandling, too. Thanks Mikael for the inspiration! You deserve the nod and the accept vote!

根据 Mikael Eriksson 的回答更新了我的回答，也包括IsNIL处理。感谢 Mikael 的灵感！你应该得到点头和接受投票！

Answer 3

回答by Ludovic Aubert

You can convert your xmlfile to jsonand then use OPENJSONinstead. Take a look at openjson : SELECT staron how to use OPENJSON without having to list column names.

您可以将xml文件转换为json然后使用OPENJSON。看看openjson : SELECT star关于如何使用 OPENJSON 而不必列出列名。

To convert an xmlfile to jsonyou can use sp_execute_external_scriptand the xmltodictpython module. Take a look at Importing Python Libraries to SQL Serverto import the python module and deal with YAPI (Yet Another Python Install) issues.

要将xml文件转换为json您可以使用sp_execute_external_script和xmltodictpython 模块。查看将 Python 库导入 SQL Server以导入 python 模块并处理 YAPI（又一个 Python 安装）问题。

在 SQL Server 中使用 OpenXML 从 XML 中提取数据

提问by Rush

回答by Mikael Eriksson

回答by marc_s

回答by Ludovic Aubert

相关推荐

最近更新

标签

在 SQL Server 中使用 OpenXML 从 XML 中提取数据

提问by Rush

回答by Mikael Eriksson

回答by marc_s

回答by Ludovic Aubert

相关推荐

SQL “IN”运算符可以在 Oracle 中使用 LIKE 通配符 (%) 吗？

在 SQL Server 中的另一个存储过程中执行一个存储过程

SQL oracle - 需要提交哪些语句？

从 SQL Server 2008 中的日期时间字段格式化月份和年份

相关推荐

最近更新

标签