Tsql Query xml columns by nodes

  • 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]??

     

    <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>

  • Try this

    s.PO.value('(/UserArea/Property/NameValue[@name="ManufacturerPart"])[1]', 'nvarchar(50)') as MFG

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Duplicate deleted

    • This reply was modified 1 year, 7 months ago by  Mark Cowne.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply