Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]...
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfran
You probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]...
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran