Tsql Query xml columns by nodes-CodePudding

I have created an parmeter @myxml and populated it with my xml. I am able to get most of the data returned but I am running in to a problem with a node called userarea. if i am select s.PO.value(':UserArea[1]/:Property[4]', 'nvarchar(50)') as MFG I can get the mfg name however if one of the propertys are not in the xml then then i get the wrong value. is there a way to call the property by its name and not the index.Property[Manufacture]??

Declare @POXML as XML
Set @POXML = '<SyncPurchaseOrder releaseID="9.2">
    <DataArea>
    <PurchaseOrder>
      <PurchaseOrderLine>
        <LineNumber>1</LineNumber>
        <UserArea>
          <Property>
            <NameValue name="ActiveFlag">true</NameValue>
          </Property>
          <Property>
            <NameValue name="ExchangeRate">1.00</NameValue>
          </Property>
          <Property>
            <NameValue name="UDFCHAR02"/>
          </Property>
          <Property>
            <NameValue name="Manufacturer">SHA</NameValue>
          </Property>
          <Property>
            <NameValue name="ManufacturerPart">16710761-001</NameValue>
          </Property>
          <Property>
            <NameValue name="TransactionNumber"/>
          </Property>
          <Property>
            <NameValue name="TransactionLine"/>
          </Property>
          <Property>
            <NameValue name="UDFNUM02">2</NameValue>
          </Property>
        </UserArea>
      </PurchaseOrderLine>
      <PurchaseOrderLine>
        <LineNumber>2</LineNumber>
         <UserArea>
          <Property>
            <NameValue name="ActiveFlag">true</NameValue>
          </Property>
          <Property>
            <NameValue name="ExchangeRate">1.00</NameValue>
          </Property>
          <Property>
            <NameValue name="UDFCHAR02"/>
          </Property>
          <Property>
            <NameValue name="Manufacturer">MIS</NameValue>
          </Property>
          <Property>
            <NameValue name="ManufacturerPart">20021676 80</NameValue>
          </Property>
          <Property>
            <NameValue name="TransactionNumber"/>
          </Property>
          <Property>
            <NameValue name="TransactionLine"/>
          </Property>
          <Property>
            <NameValue name="UDFCHAR11">18275884-001</NameValue>
          </Property>
          <Property>
            <NameValue name="UDFNUM02"/>
          </Property>
        </UserArea>
      </PurchaseOrderLine>
    </PurchaseOrder>
  </DataArea>
</SyncPurchaseOrder>'

drop table if exists #reqOnPo

select  s.PO.value('*:UserArea[1]/*:Property[4]', 'nvarchar(50)') as MFG
        --,s.PO.value('(/SyncPurchaseOrder/DataArea/PurchaseOrder/PurchaseOrderLine/UserArea/Property/NameValue[@name="Manufacturer"])[5]', 'nvarchar(max)') as MFG1
into #reqOnPo
from @POXML.nodes('./*:SyncPurchaseOrder/*:DataArea/*:PurchaseOrder/*:PurchaseOrderLine') as  s(PO)
select * from #reqonpo

I have tried putting the name in place of the index value but i get errors.

CodePudding user response：

Are you looking for this:

DECLARE @XML XML = N'<UserArea>
  <Property>
    <NameValue name="ActiveFlag">true</NameValue>
  </Property>
  <Property>
    <NameValue name="ExchangeRate">1.00</NameValue>
  </Property>
  <Property>
    <NameValue name="Manufacturer">FoxCon</NameValue>
  </Property>
  <Property>
    <NameValue name="ManufacturerPart">456889</NameValue>
  </Property>
  <Property>
    <NameValue name="TransactionNumber"/>
  </Property>
  <Property>
    <NameValue name="TransactionLine"/>
  </Property>
  <Property>
    <NameValue name="eam.UDFNUM02"/>
  </Property>
</UserArea>';

select @xml.value('(/UserArea/Property//NameValue[@name="ManufacturerPart"])[1]', 'nvarchar(max)')    -- 456889 
select @xml.value('(/UserArea/Property//NameValue[@name="eam.UDFNUM02"])[1]', 'nvarchar(max)')        -- ''
select @xml.value('(/UserArea/Property//NameValue[@name="some misssing item"])[1]', 'nvarchar(max)')  -- NULL

CodePudding user response：

    s.PO.value('*:Property[*:NameValue[@name="eam.ManufacturerPart"]][1]', 'nvarchar(50)') as MFGPart,
    s.PO.value('*:Property[*:NameValue[@name="eam.Manufacturer"]][1]', 'nvarchar(50)') as MFG1 into #reqOnPo