Retrieving Xml Attribute Using Xquery

I am using the below query to select the values of XML from attributes ad elements of the XML file but I am not able to read the seq, id, reported dated attributes from XML page s

Solution 1:

Parsing XQuery with an Xml Loose @Variable

Assuming an Xml document similar to this (viz with all the attributes on one element):

DECLARE @xmlData XML = 
N'<Reportsxmlns:x="http://foo"><x:InquiryResponse><x:ReportData><x:AccountDetails><x:Accountx:primarykey="pk"x:seq="sq"x:id="id"x:ReportedDate="2014-01-01T00:00:00" /></x:AccountDetails></x:ReportData></x:InquiryResponse></Reports>';

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);

• 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 here

Edit - Parsing Xml Column

• 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);

Updated Fiddle

Edit Xml File off Disk

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 stringsWITH 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);