sql server - Retrieving xml attribute using Xquery

Question

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 so any one please suggest How to get values of attributes using this Query.

select a_node.value('(./text())[1]', 'var char(50)') AS c_val,
c1_node.value('(./text())[1]', 'var char(50)') AS c_val 2,
ca_node.value('(./text())[1]', 'var char(50)') AS c_val3, 
d_node.value('(./text())[1]', 'var char(50)') ,
e_node.value('(./text())[1]', 'varchar(50)') ,
f_node.value('(./text())[1]', 'var char(50)') 
FROM @xmlData.nodes('/Reports/x:InquiryResponse/x:ReportData/x:AccountDetails/x:Account') AS b(b_node) 
outer APPLY b.b_node.nodes('./x:primarykey') AS pK_InquiryResponse (a_node) 
outer APPLY b.b_node.nodes('./x:seq') AS CustomerCode (c1_node) 
outer APPLY b.b_node.nodes('./x:id') AS amount (ca_node)
outer APPLY b.b_node.nodes('./x:ReportedDate') AS CustRefField (d_node)
outer APPLY b.b_node.nodes('./x:AccountNumber') AS ReportOrderNO (e_node)
outer apply b.b_node.nodes('./x:CurrentBalance') as additional_id (f_node);

Edit: Xml Snippets Provided in Comments

<sch:Account seq="2" id="345778174" ReportedDate="2014-01-01">
    <sch:AccountNumber>TSTC1595</sch:AccountNumber>
    <sch:CurrentBalance>0</sch:CurrentBalance>
    <sch:Institution>Muthoot Fincorp Limited</sch:Institution>
    <sch:PastDueAmount>0</sch:PastDueAmount>
    <sch:DisbursedAmount>12000</sch:DisbursedAmount>
    <sch:LoanCategory>JOG Group</sch:LoanCategory>
</sch:Account>

<sch:Account seq="2" id="345778174" ReportedDate="2014-01-01">
    <sch:BranchIDMFI>THRISSUR ROAD</sch:BranchIDMFI>
    <sch:KendraIDMFI>COSTCO/RECENT-107</sch:KendraIDMFI>
</sch:Account>

Answer 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'<Reports xmlns:x="http://foo">
    <x:InquiryResponse>
        <x:ReportData>
            <x:AccountDetails>
                <x:Account x: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:	empfile3098.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);