Query Out XML Data

Question

trying to query out some XML data, the data is structured like the below. (but with far more rows).

<FIELD>
  <ROW>
    <FIELD name="LI_PRODID">1</FIELD>
    <FIELD name="LI_QTY">3</FIELD>
    <FIELD name="CALC_UOM">1</FIELD>
  </ROW>
    <ROW>
        <FIELD name="LI_PRODID">2</FIELD>
        <FIELD name="LI_QTY">4</FIELD>
        <FIELD name="CALC_UOM">1</FIELD>
    </ROW>
</FIELD>

I have the following:

DECLARE @nameElement NVARCHAR(30) = '"LI_PROD"';

SELECT      
col.value('(FIELD/ROW/FIELD[name=sql:variable("@nameElement")])[1]', 'nvarchar(30)')
FROM
Table

How would I go about actually getting each "LI_PRODID" on a different row as a column, then repeating for each of the for each row?

While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Mar 11 '21 at 13:35

score 1 · Accepted Answer · answered Mar 11 '21 at 13:30

Assuming it is SQL Server.

XQuery .nodes() and .value() methods produce what you need.

You may need to adjust data types beyond INT.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<FIELD>
    <ROW>
        <FIELD name="LI_PRODID">1</FIELD>
        <FIELD name="LI_QTY">3</FIELD>
        <FIELD name="CALC_UOM">1</FIELD>
    </ROW>
    <ROW>
        <FIELD name="LI_PRODID">2</FIELD>
        <FIELD name="LI_QTY">4</FIELD>
        <FIELD name="CALC_UOM">1</FIELD>
    </ROW>
</FIELD>');
-- DDL and sample data population, end

SELECT c.value('(FIELD[@name="LI_PRODID"]/text())[1]', 'INT') AS LI_PRODID
    , c.value('(FIELD[@name="LI_QTY"]/text())[1]', 'INT') AS LI_QTY
    , c.value('(FIELD[@name="CALC_UOM"]/text())[1]', 'INT') AS CALC_UOM
FROM @tbl CROSS APPLY xmldata.nodes('/FIELD/ROW') AS t(c);

Output

+-----------+--------+----------+
| LI_PRODID | LI_QTY | CALC_UOM |
+-----------+--------+----------+
|         1 |      3 |        1 |
|         2 |      4 |        1 |
+-----------+--------+----------+

Thank you very much, this seems to have done it for me :) And learnt for future as well so that's great! — BrackleyMan, Mar 11 '21 at 16:00

score 0 · Answer 2 · answered Mar 11 '21 at 13:28

Assuming SQL Server, this should give you what you want.

declare @x xml = 
'<FIELD>
  <ROW>
    <FIELD name="LI_PRODID">1</FIELD>
    <FIELD name="LI_QTY">3</FIELD>
    <FIELD name="CALC_UOM">1</FIELD>
  </ROW>
    <ROW>
        <FIELD name="LI_PRODID">2</FIELD>
        <FIELD name="LI_QTY">4</FIELD>
        <FIELD name="CALC_UOM">1</FIELD>
    </ROW>
</FIELD>'




SELECT 
   ROWITEM.value('@name', 'varchar(10)')  as rc
from @x.nodes('(/FIELD/ROW/FIELD)') AS FIELD(ROWITEM)

Query Out XML Data

2 Answers2