处理XML数据类型(SQL SERVER 2005)

I have a table having 2 columns EmployeeId (int) and EmployeeDetails(XMl type)

我有一个表有两个列EmployeeId (int)和EmployeeDetails(XMl类型)

EmployeeId EmployeeDetails

EmployeeId EmployeeDetails

1 <Employee><EmployeeDetails><EmployeeName> Priyanka </EmployeeName><Age> 24 </Age><Address> Argentina</Address></EmployeeDetails></Employee>

1 < employedetails > Priyanka 24

阿根廷

2 <Employee><EmployeeDetails><EmployeeName> Sarkar </EmployeeName><Age> 28 </Age><Address> Australia</Address></EmployeeDetails></Employee>

2 < employedetails > Sarkar 28

Australia

I need to have the output like

我需要像这样的输出

EmployeeId EmpName Age Address

EmployeeId EmpName年龄地址

---

1 Priyanka 24 Argentina

1卡24阿根廷

2 Sarkar 28 Australia

2 Sarkar 28日澳大利亚

Please help as I am new to xml

请帮助，因为我是新手xml

1 个解决方案

#1

Books Online is a bit indecipherable on the subject of XML; you may want to try this site for help: http://blogs.msdn.com/mrorke/archive/2005/06/01/423965.aspx Here's a snippet of code to help you along.

网上的书籍在XML这个主题上有点难以理解;你可能想试试这个网站的帮助:http://blogs.msdn.com/mrorke/archive/2005/06/01/423965.aspx，这里有一段代码可以帮助你。

DECLARE @Employees TABLE
    (
      EmployeeID INT ,
      EmployeeDetails XML
    )

INSERT  INTO @Employees
        ( EmployeeID ,
          EmployeeDetails 
        )
VALUES  ( 1 ,
          '<Employee><EmployeeDetails><EmployeeName> Priyanka </EmployeeName><Age> 24 </Age><Address> Argentina</Address></EmployeeDetails></Employee>'

        )


INSERT  INTO @Employees
        ( EmployeeID ,
          EmployeeDetails 
        )
VALUES  ( 2 ,
          '<Employee><EmployeeDetails><EmployeeName> Sarkar </EmployeeName><Age> 28 </Age><Address> Australia</Address></EmployeeDetails></Employee>'
        )


SELECT  EmployeeID ,
        EmployeeName = m.value('EmployeeName[1]', 'varchar(50)') ,
        Age = m.value('Age[1]', 'int') ,
        Address = m.value('Address[1]', 'varchar(100)')
FROM    @Employees e
        CROSS APPLY EmployeeDetails.nodes('/Employee/EmployeeDetails') AS e1 ( m )