Query XML data in SQL Server 2008 R2

Question

I am trying to parse through an XML in SQL Server 2008 R2 and I am having some issues. I am trying to parse through the items for each parent node, but I'm not getting the solution The XML data is below:

<MerchantInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Active>true</Active>
  <CreatedDate>2015-04-16T00:00:00+05:30</CreatedDate>
  <CreatedBy>63747</CreatedBy>
  <ModifiedBy>63747</ModifiedBy>
  <ModifiedUserName>charucsrawat@hpcl</ModifiedUserName>
  <MerchantCode>0</MerchantCode>
  <RetailOutletName>Gayatri Automobiles</RetailOutletName>
  <DealerName>Vandana Singh</DealerName>
  <ERPCode>16622710</ERPCode>
  <OwnerID>0</OwnerID>
  <ICICIAcDetailsRequired>false</ICICIAcDetailsRequired>
  <SupplyLocationCode>Mirzapur</SupplyLocationCode>
  <IsLive>false</IsLive>
  <LiveSAM>1</LiveSAM>
  <TestSAM>0</TestSAM>
  <OutletCategory>8001</OutletCategory>
  <HighwayNo>NH7</HighwayNo>
  <HighwayName>Mirzapur Rewa Road</HighwayName>
  <SecurityDeposit>10000</SecurityDeposit>
  <HSDSaleMonthly>200</HSDSaleMonthly>
  <Comments />
  <VerifiedDate>0001-01-01T00:00:00</VerifiedDate>
  <VerifiedBy>0</VerifiedBy>
  <isCloned>false</isCloned>
  <VerifiedByUserName />
  <ApprovedDate>0001-01-01T00:00:00</ApprovedDate>
  <ApprovedBy>0</ApprovedBy>
</MerchantInfo>

my code is :

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"' AS mi)
SELECT 
    T.C.value('mi:erpcode[1]','numeric') as erpcode,
    T.C.value('mi:SecurityDeposit[1]','varchar(50)') AS securitydeposit
FROM ChangeEvent ce
    CROSS APPLY changeddata.nodes('mi/erpcode[1]') AS T(C)
WHERE Ce.EntityTypeId = 2
    AND CAST(Ce.ChangedData AS VARCHAR(MAX)) LIKE '%16622710%'
GO

What I am looking for is:

erpcode  securitydeposit
16622710  10000

Kindly help

har07 · Accepted Answer · 2015-05-20 10:28:26Z

1

You don't need ;WITH XMLNAMESPACES for this purpose since XML elements involved in the query doesn't use any namespace prefix and the XML document doesn't have defult namespace too.

Also note that XML element/attribute name are case-sensitive (f.e erpcode != ERPCode) :

SELECT 
    T.C.value('ERPCode[1]','numeric') as erpcode,
    T.C.value('SecurityDeposit[1]','varchar(50)') AS securitydeposit
FROM ChangeEvent ce
    CROSS APPLY ChangedData.nodes('MerchantInfo') AS T(C)
WHERE Ce.EntityTypeId = 2
    AND T.C.value('ERPCode[1]','numeric') = 16622710

Demo

answered May 20, 2015 at 10:28

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query XML data in SQL Server 2008 R2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related