CODEWITHSUNDEEP
sqlsql-serverxmlxquery
Stacky
Stacky

Reputation: 27

In SQL, in XML datatype field, how do I query the data like a simple sql record?

I have one XML field in table.

It stores data as

'<NewDataSet>
  <ClaimExpense>
    <ClaimNo>3003-LOB-0003</ClaimNo>
    <Office>3003</Office>
    <BranchId>1</BranchId>
    <CostCenterId>35</CostCenterId>
    <ServiceLineId>14</ServiceLineId>
    <ProjectId>62</ProjectId>
    <LCAmountCurr>AED</LCAmountCurr>
    <LCAmount>367.25</LCAmount>
    <FCCurr>USD</FCCurr>
    <FCAmount>100</FCAmount>
    <ExchangeRate>3.67252</ExchangeRate>
    <ExpenseDate>2020-11-03T00:00:00+04:00</ExpenseDate>
    <ClaimItemNo>ITM-004</ClaimItemNo>
    <GLAccount>10000000</GLAccount>
    <VatRate>5%</VatRate>
    <VatBaseAmount>349.76</VatBaseAmount>
    <VatAmount>17.49</VatAmount>
    <ClaimType>LOB</ClaimType>
    <ForPayment>357.25</ForPayment>
    <ForDeduction>0.00</ForDeduction>
    <EmpCode>2019-1194</EmpCode>
  </ClaimExpense>
  <ClaimExpense>
    <ClaimNo>3003-LOB-0003</ClaimNo>
    <Office>3003</Office>
    <BranchId>1</BranchId>

    <CostCenterId>35</CostCenterId>
    <ServiceLineId>14</ServiceLineId>
    <ProjectId>62</ProjectId>
    <LCAmountCurr>AED</LCAmountCurr>
    <LCAmount>90.00</LCAmount>
    <FCCurr>AED</FCCurr>
    <FCAmount>90</FCAmount>
    <ExchangeRate>1</ExchangeRate>
    <ExpenseDate>2020-11-03T00:00:00+04:00</ExpenseDate>
    <ClaimItemNo>ITM-005</ClaimItemNo>
    <GLAccount>10000000</GLAccount>
    <VatRate />

    <ClaimType>LOB</ClaimType>
    <ForPayment>357.25</ForPayment>
    <ForDeduction>0.00</ForDeduction>
    <EmpCode>2019-1194</EmpCode>

  </ClaimExpense>
</NewDataSet>'

Now I want to simply query it like an sql record how do I do that?

I tried with conversion and everything but no luck with that. I just want it be simply like any sql record which can be selected, inserted deleted.

Tired but no luck with this:

   SELECT  
       Tbl.Col.value('GLAccount[0]', 'varchar')
     
FROM   @xml.nodes('/NewDataSet/ClaimExpense/GLAccount') Tbl(Col)

Upvotes: 0

Views: 48

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22275

Check it out how to do it for your XML.

SQL Fiddle

SQL

CREATE table tbl (ID INT IDENTITY PRIMARY KEY, xmldata XML);
insert into tbl (xmldata) values
(
  N'<NewDataSet>
    <ClaimExpense>
        <ClaimNo>3003-LOB-0003</ClaimNo>
        <Office>3003</Office>
        <BranchId>1</BranchId>
        <CostCenterId>35</CostCenterId>
        <ServiceLineId>14</ServiceLineId>
        <ProjectId>62</ProjectId>
        <LCAmountCurr>AED</LCAmountCurr>
        <LCAmount>367.25</LCAmount>
        <FCCurr>USD</FCCurr>
        <FCAmount>100</FCAmount>
        <ExchangeRate>3.67252</ExchangeRate>
        <ExpenseDate>2020-11-03T00:00:00+04:00</ExpenseDate>
        <ClaimItemNo>ITM-004</ClaimItemNo>
        <GLAccount>10000000</GLAccount>
        <VatRate>5%</VatRate>
        <VatBaseAmount>349.76</VatBaseAmount>
        <VatAmount>17.49</VatAmount>
        <ClaimType>LOB</ClaimType>
        <ForPayment>357.25</ForPayment>
        <ForDeduction>0.00</ForDeduction>
        <EmpCode>2019-1194</EmpCode>
    </ClaimExpense>
    <ClaimExpense>
        <ClaimNo>3003-LOB-0003</ClaimNo>
        <Office>3003</Office>
        <BranchId>1</BranchId>
        <CostCenterId>35</CostCenterId>
        <ServiceLineId>14</ServiceLineId>
        <ProjectId>62</ProjectId>
        <LCAmountCurr>AED</LCAmountCurr>
        <LCAmount>90.00</LCAmount>
        <FCCurr>AED</FCCurr>
        <FCAmount>90</FCAmount>
        <ExchangeRate>1</ExchangeRate>
        <ExpenseDate>2020-11-03T00:00:00+04:00</ExpenseDate>
        <ClaimItemNo>ITM-005</ClaimItemNo>
        <GLAccount>10000000</GLAccount>
        <VatRate/>
        <ClaimType>LOB</ClaimType>
        <ForPayment>357.25</ForPayment>
        <ForDeduction>0.00</ForDeduction>
        <EmpCode>2019-1194</EmpCode>
    </ClaimExpense>
</NewDataSet>');

select c.value('(ClaimNo/text())[1]', 'VARCHAR(20)') as ClaimNo
  -- everything in between with proper data types
  , c.value('(EmpCode/text())[1]', 'VARCHAR(10)') as EmpCode
from tbl cross apply xmldata.nodes('/NewDataSet/ClaimExpense') t(c);

Upvotes: 2

Thom A
Thom A

Reputation: 95830

As I mention in the comments, you need to use the names of your nodes; your XML has no node "row" so of course SQL Server isn't going to find any data. I also recommend use the text() function, as it is far more efficient. For example:

SELECT NDS.CE.value('(ClaimNo/text())[1]','int') AS ClaimNo
FROM @XML.nodes('NewDataSet/ClaimExpense') NDS(CE);

Upvotes: 1

Related Questions