Select XML nodes as rows
Here is your solution:
/* TEST TABLE */
DECLARE @PEOPLE AS TABLE ([Name] VARCHAR(20), [Address] XML )
INSERT INTO @PEOPLE SELECT
'Joel',
'<address>
<street>Street 1</street>
<city>City 1</city>
<state>State 1</state>
<zipcode>Zip Code 1</zipcode>
</address>
<address>
<street>Street 2</street>
<city>City 2</city>
<state>State 2</state>
<zipcode>Zip Code 2</zipcode>
</address>'
UNION ALL SELECT
'Kim',
'<address>
<street>Street 3</street>
<city>City 3</city>
<state>State 3</state>
<zipcode>Zip Code 3</zipcode>
</address>'
SELECT * FROM @PEOPLE
-- BUILD XML
DECLARE @x XML
SELECT @x =
( SELECT
[Name]
, [Address].query('
for $a in //address
return <address
street="{$a/street}"
city="{$a/city}"
state="{$a/state}"
zipcode="{$a/zipcode}"
/>
')
FROM @PEOPLE AS people
FOR XML AUTO
)
-- RESULTS
SELECT [Name] = T.Item.value('../@Name', 'varchar(20)'),
street = T.Item.value('@street' , 'varchar(20)'),
city = T.Item.value('@city' , 'varchar(20)'),
state = T.Item.value('@state' , 'varchar(20)'),
zipcode = T.Item.value('@zipcode', 'varchar(20)')
FROM @x.nodes('//people/address') AS T(Item)
/* OUTPUT*/
Name | street | city | state | zipcode
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Joel | Street 1 | City 1 | State 1 | Zip Code 1
Joel | Street 2 | City 2 | State 2 | Zip Code 2
Kim | Street 3 | City 3 | State 3 | Zip Code 3
Convert XML Nodes To Rows in SQL Server
Replace T.r.query('.') as id with T.r.value('.', 'INT') as id
Read XML nodes as rows in SQL Server
WITH XMLNAMESPACES (DEFAULT 'urn:www:bebe:de:b2b:v01')
SELECT
d.p.value('./Identification[1]', 'BIGINT') AS [Identification]
,d.p.value('./StatusType[1]', 'NVARCHAR(100)') AS [StatusType]
,d.p.value('(./StatusType/@listAgencyIdentifier)[1]', 'int') AS [ListAgencyIdentifier]
,d.p.value('./OriginalDocument[1]', 'BIGINT') AS [OriginalDocument]
FROM [yourtable] [t]
CROSS APPLY [t].[yourcolumn].nodes('//PayloadResponseEvent') AS d(p)
Select node from xml nodes
Use the query method
Declare @xmlDoc xml = '
<ROW ID="471869" USER_ID="40161" NAME="James" CNT="2" />
<ROW ID="482047" USER_ID="40836" NAME="Nick" CNT="2" />
'
SELECT x.value('(@ID)[1]', 'int') AS ID,
x.value('(@USER_ID)[1]', 'int') AS USER_ID,
x.value('(@NAME)[1]', 'varchar(100)') AS NAME,
x.value('(@CNT)[1]', 'int') AS CNT,
x.query('.') cc
from @xmlDoc.nodes('/ROW') AS t(x)
cc type is XML.
db<>fiddle
Select all XML nodes from XML column
The following will transform your flat XML without a root node into a classical EAV list:
DECLARE @tbl TABLE(ID INT IDENTITY, YourXml XML);
INSERT INTO @tbl VALUES
('<tag1>1</tag1><tag2>2</tag2>')
,('<tag3>3</tag3><tag4>4</tag4><tag5>5</tag5>');
--The query
SELECT t.ID
,AllNodes.value('local-name(.)','nvarchar(max)') AS TagName
,AllNodes.value('text()[1]','nvarchar(max)') AS TagValue
FROM @tbl t
CROSS APPLY t.YourXml.nodes('/*') A(AllNodes);
The XQuery function local-name() allows to query for meta data, the text() node represents the element's content.
TSQL XML - Node attributes as columns or rows in same query
Since originally you did not provided DDL+DML, I am presents two samples. One for table which has identification column (ID in my sample) and one without (which mean that I need to add one dynamically using ROW_NUMBER)
Demo one: When we have identification column
-- DDL+DML : this is something that the OP should provide!!!
DROP TABLE IF EXISTS MyXML2
GO
CREATE TABLE MyXML2(ID INT IDENTITY(1,1), c XML)
INSERT MyXML2(c) VALUES
('<Step No="1" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers1</Step>'),
('<Step No="11" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers1</Step>'),
('<Step No="2" Types="D2" Temp="22" Secs="62" Macro="23">Enable Mixers3</Step>')
GO
-- Solution
;With MyCTE as (
SELECT
MyXML2.ID,
doc.Col.value('local-name(.[1])','VARCHAR(100)') ColumnName,
doc.Col.value('.[1]','VARCHAR(100)') ColumnValue
FROM MyXML2
CROSS APPLY MyXML2.c.nodes('/Step/@*') doc(Col)
)
select
StepNumber = (SELECT MyIn.ColumnValue from MyCTE as MyIn where MyIn.ColumnName = 'No' and MyIn.ID = MyCTE.ID)
,ColumnName,ColumnValue
from MyCTE
WHERE not ColumnName = 'No'
GO
Demo two: When we do not have identification column(s)
-- DDL+DML : this is something that the OP should provide!!!
DROP TABLE IF EXISTS MyXML
GO
CREATE TABLE MyXML(c XML)
INSERT MyXML(c) VALUES
('<Step No="1" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers1</Step>'),
('<Step No="11" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers1</Step>'),
('<Step No="2" Types="D2" Temp="22" Secs="62" Macro="23">Enable Mixers3</Step>')
GO
-- Solution
;With MyCTE1 AS (SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), c FROM MyXML)
, MyCTE2 as (
SELECT
MyCTE1.RN,
doc.Col.value('local-name(.[1])','VARCHAR(100)') ColumnName,
doc.Col.value('.[1]','VARCHAR(100)') ColumnValue
FROM MyCTE1
CROSS APPLY MyCTE1.c.nodes('/Step/@*') doc(Col)
)
select
StepNumber = (SELECT MyIn.ColumnValue from MyCTE2 as MyIn where MyIn.ColumnName = 'No' and MyIn.RN = MyCTE2.RN)
,ColumnName,ColumnValue
from MyCTE2
WHERE not ColumnName = 'No'
GO
Result as expected:
Update: 2021-12-06
Following the new information which we got, here are some new solutions and explanation. The above should be useful for future readers which have similar question.
So, in the above solutions I focused on a case where we have single Step node in each row in the table. According the new information we might have multiples nodes of Step in the same value. Moreover, the Step nodes are wrapped in another node name Process
For example, a specific XML value can be: <Process><Step No="1" Types="D1" Temp="1" Secs="61" Macro="21">Enable Mixers1</Step> <Step No="11" Types="D11" Temp="11" Secs="611" Macro="21">Enable Mixers2</Step> <Step No="111" Types="D111" Temp="111" Secs="6111" Macro="23">Enable Mixers3</Step></Process>
Demo three: using variable, Step nodes structure is unknown, multiple Step nodes
In this demo I will resent solution based on the same approach as solution one
declare @xml XML = '<Process><Step No="1" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers1</Step>
<Step No="11" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers2</Step>
<Step No="2" Types="D2" Temp="22" Secs="62" Macro="23">Enable Mixers3</Step></Process>'
-->>> HIGHLY recommended to un-comment below lines and check what I am using as input for the CTE in this solution
--SELECT
-- t.c.value('./@No', 'VARCHAR(128)') as StepNumber,
-- t.c.query ('.') as Types
--from @xml.nodes('Process/.[1]/*')as t(c)
;With MyCTE01 as (
SELECT
t.c.value('./@No', 'INT') as StepNumber,
t.c.query ('.') as MyXML
from @xml.nodes('Process/.[1]/*')as t(c)
)
SELECT
MyCTE01.StepNumber,
doc.Col.value('local-name(.[1])','VARCHAR(100)') ColumnName,
doc.Col.value('.[1]','VARCHAR(100)') ColumnValue
FROM MyCTE01
CROSS APPLY MyCTE01.MyXML.nodes('/Step/@*') doc(Col)
WHERE not doc.Col.value('local-name(.[1])','VARCHAR(100)') = 'No'
GO
This solution will work for you but if the structure of the Step node is always the same - meaning you have the same attributes as in al the examples during the discussion, then we can get much much better solutions...
Demo four: Using variable, Step nodes has a known structure, multiple Step nodes
Since we know which attribute we have then we can hard coded use the names. In this case we do not this part which meant to find all the attributes CROSS APPLY MyCTE01.MyXML.nodes('/Step/@*')
We can use totally different approach, directly getting the values of the know attributes and using UNPIVOT. This solution provide much better performance but it is less flexible then solutions three.
declare @xml XML = '<Process><Step No="1" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers1</Step>
<Step No="11" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers2</Step>
<Step No="2" Types="D2" Temp="22" Secs="62" Macro="23">Enable Mixers3</Step></Process>'
--select
-- t.c.value('./@No', 'VARCHAR(128)') as id,
-- t.c.value('./@Types', 'VARCHAR(128)') as Types,
-- t.c.value('./@Temp', 'VARCHAR(128)') as Temp,
-- t.c.value('./@Secs', 'VARCHAR(128)') as Secs,
-- t.c.value('./@Macro', 'VARCHAR(128)') as Macro,
-- t.c.value('./@Macro', 'VARCHAR(128)') as Macro
--from @xml.nodes('Process/.[1]/*')as t(c)
SELECT StepNumber, Column_Name, Column_Value
FROM(
select
t.c.value('./@No', 'VARCHAR(128)') as StepNumber,
t.c.value('./@Types', 'VARCHAR(128)') as Types,
t.c.value('./@Temp', 'VARCHAR(128)') as Temp,
t.c.value('./@Secs', 'VARCHAR(128)') as Secs,
t.c.value('./@Macro', 'VARCHAR(128)') as Macro
from @xml.nodes('Process/.[1]/*')as t(c)
) p
UNPIVOT
(Column_Value FOR Column_Name IN (Types, Temp, Secs, Macro) )AS unpvt;
GO
Note! You can use this approach for unknown structure as well if you use dynamic queries and first find the attributes in the XML.
Demo five: Using variable, Step nodes has a known structure, multiple Step nodes
This solution has the same limitation as solution four (known structure) but in addition it only fits when we are working on single value like a variable. Therefore, if we want to implement it on table then we might need to loop all rows which might reduce performance dramatically. But when this solution fits the needs then it should provide best performance!
/***BEST SOLUTION - if fits the needs***/
-- XML to Tabular using OPENXML
DECLARE @idoc INT, @xml XML = '<Process><Step No="1" Types="D1" Temp="1" Secs="61" Macro="21">Enable Mixers1</Step>
<Step No="11" Types="D11" Temp="11" Secs="611" Macro="21">Enable Mixers2</Step>
<Step No="111" Types="D111" Temp="111" Secs="6111" Macro="23">Enable Mixers3</Step></Process>'
--Create an internal representation of the XML document.
-- Reads the XML text -> parses the text by using the MSXML parser -> and provides the parsed document in a state ready for consumption.
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml;
--SELECT
-- No as StepNumber,
-- Types as Types,
-- Temp as Temp,
-- Secs as Secs,
-- Macro as Macro,
-- NoteValue
--FROM OPENXML (@idoc, '/Process/Step')
-- WITH (
-- -- When OPENXML does not have input of third parameter then we can choose if this will atribute or node
-- -- usig '@No' will bring the value of atribute and using 'No' will bring the value of node
-- No INT '@No' ,
-- Types VARCHAR(128) '@Types',
-- Temp VARCHAR(128) '@Temp' ,
-- Secs VARCHAR(128) '@Secs' ,
-- Macro VARCHAR(128) '@Macro',
-- NoteValue VARCHAR(128) '.'
-- )
SELECT StepNumber, Column_Name, Column_Value
FROM(
SELECT
No as StepNumber,
Types as Types,
Temp as Temp,
Secs as Secs,
Macro as Macro
FROM OPENXML (@idoc, '/Process/Step',1)
WITH (
No INT,
Types VARCHAR(128),
Temp VARCHAR(128),
Secs VARCHAR(128),
Macro VARCHAR(128)
)
) p
UNPIVOT
(Column_Value FOR Column_Name IN (Types, Temp, Secs, Macro) )AS unpvt;
--sp_xml_removedocument free's up the memory.
EXEC sp_xml_removedocument @idoc
GO
So... we have multiple approaches whic fits different case... but we still need to think about tables...
Demo six: Using table, Step nodes has unknown structure, multiple Step nodes
You can implement Demo four if this fit (known structure or using dynamic query), but for the last demo I will implement Demo three approach on a case that we have multiple rows in a table which each row includes XML that has multiple Step nodes
DROP TABLE IF EXISTS MyXML_Tbl
GO
CREATE TABLE MyXML_Tbl(ID INT IDENTITY(1,1), MyXML XML)
GO
INSERT MyXML_Tbl(MyXML) VALUES
('<Process><Step No="1" Types="D1" Temp="1" Secs="61" Macro="21">Enable Mixers1</Step>
<Step No="11" Types="D11" Temp="11" Secs="611" Macro="21">Enable Mixers1</Step>
<Step No="111" Types="D111" Temp="111" Secs="6111" Macro="23">Enable Mixers3</Step></Process>')
INSERT MyXML_Tbl(MyXML) VALUES
('<Process><Step No="2" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers1</Step>
<Step No="22" Types="D1" Temp="11" Secs="61" Macro="21">Enable Mixers1</Step>
<Step No="222" Types="D2" Temp="22" Secs="62" Macro="23">Enable Mixers3</Step></Process>')
GO
--SELECT * FROM MyXML_Tbl
--GO
--SELECT
-- tb.ID,
-- tx.c.value('./@No', 'VARCHAR(128)') as StepNumber,
-- tx.c.query ('.') as Types
--from MyXML_Tbl tb
--CROSS APPLY tb.MyXML.nodes('Process/.[1]/*')as tx(c)
;With MyCTE01 as (
SELECT
tb.ID,
tx.c.value('./@No', 'VARCHAR(128)') as StepNumber,
tx.c.query ('.') as MyXML
from MyXML_Tbl tb
CROSS APPLY tb.MyXML.nodes('Process/.[1]/*')as tx(c)
)
SELECT
MyCTE01.id,
MyCTE01.StepNumber,
doc.Col.value('local-name(.[1])','VARCHAR(100)') ColumnName,
doc.Col.value('.[1]','VARCHAR(100)') ColumnValue
FROM MyCTE01
CROSS APPLY MyCTE01.MyXML.nodes('/Step/@*') doc(Col)
WHERE not doc.Col.value('local-name(.[1])','VARCHAR(100)') = 'No'
GO
I hope this is useful. It should cover all cases mentioned in the discussion
How select XML fields node for all rows
How about this:
SELECT
Year,
E.P.value('(ID)[1]', 'INT') AS 'ID',
E.P.value('(Name)[1]', 'VARCHAR(50)') AS 'Name',
E.P.value('(LastName)[1]', 'VARCHAR(50)') AS 'LastName'
FROM
dbo.YourTable
CROSS APPLY
Fields.nodes('/Employees/Person') AS E(P)
You're basically selecting Year from the base table and then extracting each <Person> node from the Fields column into an "inline XML table" called E with a single XML column called P (you can choose whatever names you like for those) that you again query and extract the individual elements from .
Separate XML node values into separate rows in SQL
When you have the XML in a variable:
DECLARE @x XML = '
<StudentGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<StudentIds>
<int>3000</int>
<int>3001</int>
<int>3002</int>
<int>8</int>
<int>9</int>
</StudentIds>
</StudentGroup>';
SELECT
n.v.value('.','INT') AS ID
FROM
@x.nodes('/StudentGroup/StudentIds/int') AS n(v);
When you have the XML in a table:
DECLARE @x XML = '
<StudentGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<StudentIds>
<int>3000</int>
<int>3001</int>
<int>3002</int>
<int>8</int>
<int>9</int>
</StudentIds>
</StudentGroup>';
DECLARE @t TABLE(
x XML
);
INSERT INTO @t(x)VALUES(@x);
SELECT
n.v.value('.','INT') AS ID
FROM
@t
CROSS APPLY x.nodes('/StudentGroup/StudentIds/int') AS n(v);
Querying multiple rows of an XML column to extract child nodes into multiple rows
Please try the following solution.
SQL
-- DDL and sample data population, start
DECLARE @tbl table (ID INT IDENTITY PRIMARY KEY, Name VARCHAR(20), Message XML);
INSERT INTO @tbl (Name, Message) VALUES
('John', N'<User><Data><ValueList><Value>123</Value><Value>456</Value><Value>789</Value><Value>654</Value></ValueList></Data></User>'),
('Jack', N'<User><Data><ValueList><Value>555</Value><Value>455</Value></ValueList></Data></User>'),
('Jane', N'<User><Data><ValueList><Value>576</Value><Value>854</Value><Value>933</Value></ValueList></Data></User>');
-- DDL and sample data population, end
SELECT t.ID, t.Name
, c.value('(./text())[1]', 'nvarchar(10)') AS [Values]
FROM @tbl AS t
CROSS APPLY Message.nodes('/User/Data/ValueList/Value') as t1(c);
Output
+----+------+--------+
| ID | Name | Values |
+----+------+--------+
| 1 | John | 123 |
| 1 | John | 456 |
| 1 | John | 789 |
| 1 | John | 654 |
| 2 | Jack | 555 |
| 2 | Jack | 455 |
| 3 | Jane | 576 |
| 3 | Jane | 854 |
| 3 | Jane | 933 |
+----+------+--------+
Related Topics
Ora-06502: Pl/Sql: Numeric or Value Error: Character String Buffer Too Small
Check If a Variable Is Null in Plsql
SQL - Subquery in Aggregate Function
Sort by Day of The Week from Monday to Sunday
Sql Server Left Join and Where Clause
Sql Server Server Query - Count Distinct Datetime Field
How to Get Array/Bag of Elements from Hive Group by Operator
Selecting Multiple Rows by Id, Is There a Faster Way Than Where In
Excel Vlookup Incorporating SQL Table
Concatenate with Null Values in Sql
How to Remove SQL Azure Data Sync Objects Manually
Arel Causing Infinite Loop on Aggregation
Sql Server - Return Schema for Sysobjects
Ms Access - Execute a Saved Query by Name in Vba
Update Multiple Records in Sql
How to Select The Record with The 2Nd Highest Salary in Database Oracle