CODEWITHSUNDEEP
sql-servert-sql
Muhammad Hossain
Muhammad Hossain

Reputation: 11

Get All data from XML using SQL Query

Can anyone help?

I have the following XML file, and I want to get all:

I tried many different ways but failed to retrieve data from SQL Query.

<Envelope xmlns="http://schemas.microsoft.com/dynamics/2011/01/documents/Message">
  <Header>
    <MessageId>{D5AAFEB2-CD15-4ACF-ABA2-E5F10A49BEBA}</MessageId>
    <Action>http://schemas.microsoft.com/dynamics/2011/01/services/AAFixedAssetService/find</Action>
  </Header>
  <Body>
    <MessageParts xmlns="http://schemas.microsoft.com/dynamics/2011/01/documents/Message">
      <AAFixedAsset xmlns="http://schemas.microsoft.com/dynamics/2008/01/documents/AAFixedAsset">
        <DocPurpose>Original</DocPurpose>
        <SenderId>amau</SenderId>
        <ValidAsOfDateTime>2017-02-09T17:23:44Z</ValidAsOfDateTime>
        <ValidTimeStateType>AsOf</ValidTimeStateType>
        <AssetTable class="entity">
          <_DocumentHash>4fd64a2258e0b81d684de1cc5f0248c5</_DocumentHash>
          <AAIsSent>No</AAIsSent>
          <AssetId>BU00001</AssetId>
          <WorkerResponsible>9998</WorkerResponsible>
          <AssetBook class="entity">
            <AcquisitionDate>2017-01-05</AcquisitionDate>
            <AcquisitionPrice>100.00</AcquisitionPrice>
            <AssetId>BU00001</AssetId>
            <BookId>ST</BookId>
            <DisposalDate>2017-01-14</DisposalDate>
          </AssetBook>
          <HcmWorker class="entity">
            <Person>000001292</Person>
            <PersonnelNumber>9998</PersonnelNumber>
            <RecId>5637151331</RecId>
            <DirPersonName class="entity">
              <FirstName>Amsa</FirstName>
              <LastName>Sampathkumar test</LastName>
            </DirPersonName>
          </HcmWorker>
          <AAAssetTable class="entity">
            <SubGroupId>02</SubGroupId>
          </AAAssetTable>
        </AssetTable>
        <AssetTable class="entity">
          <_DocumentHash>94dab7dab57d3e270668726992deaab7</_DocumentHash>
          <AAIsSent>No</AAIsSent>
          <AssetId>CP00001</AssetId>
          <WorkerResponsible>74</WorkerResponsible>
          <AssetBook class="entity">
            <AcquisitionDate>2017-01-06</AcquisitionDate>
            <AssetId>CP00001</AssetId>
            <BookId>ST</BookId>
          </AssetBook>
          <HcmWorker class="entity">
            <Person>000000981</Person>
            <PersonnelNumber>74</PersonnelNumber>
            <RecId>5637149826</RecId>
            <DirPersonName class="entity">
              <FirstName>Arun</FirstName>
            </DirPersonName>
          </HcmWorker>
          <AAAssetTable class="entity">
            <SubGroupId>01</SubGroupId>
          </AAAssetTable>
        </AssetTable>
      </AAFixedAsset>
    </MessageParts>
  </Body>
</Envelope>

Upvotes: 1

Views: 55

Answers (1)

Andrea
Andrea

Reputation: 12355

You can use .Value with xpath queries. You also have to define the namespaces:

declare @xml xml

set @xml='<Envelope xmlns="http://schemas.microsoft.com/dynamics/2011/01/documents/Message"> <Header> <MessageId>{D5AAFEB2-CD15-4ACF-ABA2-E5F10A49BEBA}</MessageId> <Action>http://schemas.microsoft.com/dynamics/2011/01/services/AAFixedAssetService/find</Action> </Header> <Body> <MessageParts xmlns="http://schemas.microsoft.com/dynamics/2011/01/documents/Message"> <AAFixedAsset xmlns="http://schemas.microsoft.com/dynamics/2008/01/documents/AAFixedAsset"> <DocPurpose>Original</DocPurpose> <SenderId>amau</SenderId> <ValidAsOfDateTime>2017-02-09T17:23:44Z</ValidAsOfDateTime> <ValidTimeStateType>AsOf</ValidTimeStateType> <AssetTable class="entity"> <_DocumentHash>4fd64a2258e0b81d684de1cc5f0248c5</_DocumentHash> <AAIsSent>No</AAIsSent> <AssetId>BU00001</AssetId> <WorkerResponsible>9998</WorkerResponsible> <AssetBook class="entity"> <AcquisitionDate>2017-01-05</AcquisitionDate> <AcquisitionPrice>100.00</AcquisitionPrice> <AssetId>BU00001</AssetId> <BookId>ST</BookId> <DisposalDate>2017-01-14</DisposalDate> </AssetBook> <HcmWorker class="entity"> <Person>000001292</Person> <PersonnelNumber>9998</PersonnelNumber> <RecId>5637151331</RecId> <DirPersonName class="entity"> <FirstName>Amsa</FirstName> <LastName>Sampathkumar test</LastName> </DirPersonName> </HcmWorker> <AAAssetTable class="entity"> <SubGroupId>02</SubGroupId> </AAAssetTable> </AssetTable> <AssetTable class="entity"> <_DocumentHash>94dab7dab57d3e270668726992deaab7</_DocumentHash> <AAIsSent>No</AAIsSent> <AssetId>CP00001</AssetId> <WorkerResponsible>74</WorkerResponsible> <AssetBook class="entity"> <AcquisitionDate>2017-01-06</AcquisitionDate> <AssetId>CP00001</AssetId> <BookId>ST</BookId> </AssetBook> <HcmWorker class="entity"> <Person>000000981</Person> <PersonnelNumber>74</PersonnelNumber> <RecId>5637149826</RecId> <DirPersonName class="entity"> <FirstName>Arun</FirstName> </DirPersonName> </HcmWorker> <AAAssetTable class="entity"> <SubGroupId>01</SubGroupId> </AAAssetTable> </AssetTable> </AAFixedAsset> </MessageParts> </Body> </Envelope>'
;
WITH XMLNAMESPACES ('http://schemas.microsoft.com/dynamics/2011/01/documents/Message' as ns1,
                    'http://schemas.microsoft.com/dynamics/2008/01/documents/AAFixedAsset' as ns2 ) 
select @xml.value('(/ns1:Envelope/ns1:Body/ns1:MessageParts/ns2:AAFixedAsset/ns2:AssetTable/ns2:AssetId)[1]', 'varchar(max)') as AssetId
,@xml.value('(/ns1:Envelope/ns1:Body/ns1:MessageParts/ns2:AAFixedAsset/ns2:AssetTable/ns2:AssetBook/ns2:AcquisitionDate)[1]', 'varchar(max)') as AcquisitionDate
,@xml.value('(/ns1:Envelope/ns1:Body/ns1:MessageParts/ns2:AAFixedAsset/ns2:AssetTable/ns2:HcmWorker/ns2:DirPersonName/ns2:FirstName)[1]', 'varchar(max)') as FirstName
,@xml.value('(/ns1:Envelope/ns1:Body/ns1:MessageParts/ns2:AAFixedAsset/ns2:AssetTable/ns2:HcmWorker/ns2:DirPersonName/ns2:LastName)[1]', 'varchar(max)') as LastName

Result:

enter image description here

Upvotes: 1

Related Questions