how to return element attribute value

Question

Here's an example, I'm trying to return the value attribute value:

IF OBJECT_ID(N'tempdb.dbo.#XMLwithOpenXML') IS NOT NULL
    DROP TABLE #XMLwithOpenXML;

CREATE TABLE #XMLwithOpenXML
    (
      [XMLData] [XML] NULL ,
      [LoadedDateTime] [DATETIME] NULL,
    );

INSERT  INTO #XMLwithOpenXML
        ( XMLData
        )
VALUES  ( '<SupplyWeb_Data>
  <PurchaseOrder>
    <PO_Header>
      <status value="stat" />
      <po_number value="PO123" />
    </PO_Header>
    <PO_Detail>
      <cust_part_no value="A123" />
      <cust_part_desc value="A123 Desc" />
    </PO_Detail>
    <PO_Detail>
      <cust_part_no value="B123" />
      <cust_part_desc value="B123 Desc" />
    </PO_Detail>
  </PurchaseOrder>
</SupplyWeb_Data>'
        );

SELECT  T2.Loc.value('(/cust_part_no/@Value)[1]', 'varchar(255)') AS cust_part_no ,
        T2.Loc.value('(/cust_part_desc/@Value)[1]', 'varchar(255)') AS cust_part_desc
FROM    #XMLwithOpenXML
        CROSS APPLY XMLData.nodes('/SupplyWeb_Data/PurchaseOrder/PO_Detail')
        AS T2 ( Loc );

I'm missing something easy, but I can't figure it out. I think I need to use the values function, but it's returning null.

score 2 · Accepted Answer · answered Apr 05 '16 at 12:12

2

XQuery is case sensitive so value not Value and add . before /cust_part_....

SELECT T2.Loc.value('(./cust_part_no/@value)[1]', 'varchar(255)')   AS cust_part_no 
      ,T2.Loc.value('(./cust_part_desc/@value)[1]', 'varchar(255)') AS cust_part_desc
FROM #XMLwithOpenXML x
CROSS APPLY XMLData.nodes('/SupplyWeb_Data/PurchaseOrder/PO_Detail')AS T2(Loc);

LiveDemo

answered Apr 05 '16 at 12:12

Lukasz Szozda

162,964
23
234
275

1

brilliant stuff @lad – mohan111 Apr 05 '16 at 12:15

how to return element attribute value

1 Answers1