使用 Xquery 检索 xml 属性

You can scrape the attributes out as follows:

WITH XMLNAMESPACES('http://foo' AS x)
select 
    Nodes.node.value('(@x:primarykey)[1]', 'varchar(50)') AS c_val,
    Nodes.node.value('(@x:seq)[1]', 'varchar(50)') AS c_val2,
    Nodes.node.value('(@x:id)[1]', 'varchar(50)') AS c_val3, 
    Nodes.node.value('(@x:ReportedDate)[1]', 'DATETIME') as someDateTime
FROM 
   @xmlData.nodes('/Reports/x:InquiryResponse/x:ReportData/x:AccountDetails/x:Account') 
   AS Nodes(node);

• 属性不需要 text() 因为它们自动是字符串
• 在命名空间中具有属性是相当不寻常的 - 如果没有，请删除 xmlns 别名前缀.
• Attributes don't need text() as they are automatically strings
• It is fairly unusual to have attributes in a namespace - drop the xmlns alias prefix if they aren't.

此处为 SqlFiddle

编辑 - 解析 Xml 列

• 从属性中删除的命名空间- 假设你有一个表中的数据，而不是一个变量，因此 APPLY 要求.请注意， OUTER APPLY 将返回空值，例如仅当您有行时有用空 Xml 或缺少 Xml 元素.CROSS APPLY 是常态(即将 xpath 应用于 LHS 表上选定的每一行)
• 元素的访问方式类似于属性，只是没有@
• Namespace dropped from the attributes -Assumed that you have the data in a table, not a variable, hence the APPLY requirement. Note that OUTER APPLY will return nulls, e.g. useful only if you have rows with empty Xml or missing Xml Elements. CROSS APPLY is the norm (viz applying the xpath to each row selected on the LHS table)
• Elements are accessed similar to attributes, just without @




WITH XMLNAMESPACES('http://foo' AS x)
select 
    Nodes.node.value('(@seq)[1]', 'varchar(50)') AS c_val2,
    Nodes.node.value('(@id)[1]', 'varchar(50)') AS c_val3, 
    Nodes.node.value('(@ReportedDate)[1]', 'DATETIME') as someDateTime,
    Nodes.node.value('(x:AccountNumber)[1]', 'VARCHAR(50)') as accountNumber
FROM 
    MyXmlData z
    CROSS APPLY
z.XmlColumn.nodes('/Reports/x:InquiryResponse/x:ReportData/x:AccountDetails/x:Account') 
      AS Nodes(node);

更新小提琴

从磁盘编辑 Xml 文件

这是从磁盘读取的 xml 文件的相同内容.请注意，一旦您在 XML 变量 (@MyXmlData) 中拥有数据，您就不需要 CROSS APPLY 到任何东西 - 只需提供xpath 选择合适的节点，然后刮取元素和属性.

Here's the same thing for an xml file read from disk. Note that once you have the data in an XML variable (@MyXmlData) that you don't need to CROSS APPLY to anything - just supply xpath to select the appropriate node, and then scrape out the elements and attributes.

DECLARE @MyXmlData XML;
SET @MyXmlData = 
( SELECT * FROM OPENROWSET ( BULK N'c:\temp\file3098.xml', SINGLE_CLOB ) AS MyXmlData );
-- Assuming all on the one element, no need for all the applies
-- attributes don't have a text axis (they are automatically strings

WITH XMLNAMESPACES('http://foo' AS x)
select 
    Nodes.node.value('(@seq)[1]', 'varchar(50)') AS c_val2,
    Nodes.node.value('(@id)[1]', 'varchar(50)') AS c_val3, 
    Nodes.node.value('(@ReportedDate)[1]', 'DATETIME') as someDateTime,
    Nodes.node.value('(x:AccountNumber)[1]', 'VARCHAR(50)') as accountNumber
FROM 
      @MyXmlData.nodes('/Reports/x:InquiryResponse/x:ReportData/x:AccountDetails/x:Account') 
      AS Nodes(node);