CODEWITHSUNDEEP
sql-serverxmlsql-server-2008-r2
mehdi lotfi
mehdi lotfi

Reputation: 11571

SQL Server XML Column Elements

I use SQL Server 2008 R2 and have a XML column.

I want to have query to get attribute name from a XML column.

For Example the data of XML column like below :

<Document DocumentID="214798" Date="02/15/2014 12:00:00 ق.ظ" 
          SalesCompanyFinancialPeriodID="123" DocumentSubTypeID="162" 
          Number="12427" Title="2" OwnerPersonID="50599" />

I want to get following result :

DocumentID
Date
SalesCompanyFinancialPeriodID
DocumentSubTypeID
Number
Title
OwnerPersonID

Thanks in advance

Upvotes: 2

Views: 85

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT
    xmlColumn.value('(//@DocumentID)[1]', 'int') DocumentID,
    xmlColumn.value('(//@Date)[1]', 'datetime') Date,
    xmlColumn.value('(//@SalesCompanyFinancialPeriodID)[1]', 'int') SalesCompanyFinancialPeriodID,
    xmlColumn.value('(//@DocumentSubTypeID)[1]', 'int') DocumentSubTypeID,
    xmlColumn.value('(//@Number)[1]', 'int') Number,
    xmlColumn.value('(//@OwnerPersonID)[1]', 'int') OwnerPersonID,
FROM tbl

Note, that the datatime conversion is neutral specific.

UPDATE

DECLARE @xml XML = '<Document DocumentID="214798" Date="02/15/2014 12:00 aa" SalesCompanyFinancialPeriodID="123" DocumentSubTypeID="162" Number="12427" Title="2" OwnerPersonID="50599" />'

SELECT
    name.value('local-name(.)', 'varchar(100)')
FROM
@xml.nodes('//@*') A(name)

Upvotes: 1

Related Questions