DECLARE @XmlData xml
SET @XmlData= '<?xml version="1.0" encoding="utf-8" ?>
<ROOT PROCESS_DATE="25-Nov-2009" ROW_COUNT="2" VERIFY_TOTAL="654454.54">
<row rowNumber="1">
<Code1>11111</Code1>
<Code2>AAAA </Code2>
</row>
<row rowNumber="2">
<Code1>2222</Code1>
<Code2>BBBB </Code2>
</row>
</ROOT>'
-- Following query returns the Code1, & Code2 for each row.
SELECT
R.i.query('Code1').value('.', 'nvarchar(8)') AS Code1,
R.i.query('Code2').value('.', 'nvarchar(8)') AS Code2
FROM @XmlData.nodes('/ROOT/row') R(i)
-- Now I want to get the rowNumber attribute of each row in the resultset.
SELECT
R.i.query('@rowNumber') AS rowNumber,
R.i.query('Code1').value('.', 'nvarchar(8)') AS Code1,
R.i.query('Code2').value('.', 'nvarchar(8)') AS Code2
FROM @XmlData.nodes('/ROOT/row') R(i)
-- Above query returns error
Sql – How to read an attribute of the XML
sqlxquery
Related Topic
- Php – How to prevent SQL injection in PHP
- Sql – How to list the tables in a SQLite database file that was opened with ATTACH
- Sql – How to return only the Date from a SQL Server DateTime datatype
- Sql – How to concatenate text from multiple rows into a single text string in SQL Server
- Sql – How to UPDATE from a SELECT in SQL Server
- Sql – How to query MongoDB with “like”
- Mysql – How to import an SQL file using the command line in MySQL
Best Answer