How do you read XML column in SQL Server 2008?
Try something like this:
SELECT
Cust.value('(ItemId)[1]', 'int') AS 'ItemID',
Cust.value('(Value)[1]', 'Varchar(50)') AS 'Customer Name'
FROM
dbo.Sales.CustomerList.nodes('/ArrayOfCustomers/Customer') AS AOC(Cust)
That should give you an output something like this:
ItemID Customer Name
1 Mr Smith
2 Mr Bloggs
How to query xml column in tsql
How about this?
SELECT
EventID, EventTime,
AnnouncementValue = t1.EventXML.value('(/Event/Announcement/Value)[1]', 'decimal(10,2)'),
AnnouncementDate = t1.EventXML.value('(/Event/Announcement/Date)[1]', 'date')
FROM
dbo.T1
WHERE
t1.EventXML.exist('/Event/Indicator/Name[text() = "GDP"]') = 1
It will find all rows where the /Event/Indicator/Name
equals GDP
and then it will display the <Announcement>/<Value>
and <Announcement>/<Date>
for those rows.
See SQLFiddle demo
Select values from XML field in SQL Server 2008
Given that the XML field is named 'xmlField'...
SELECT
[xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
[xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]
Read XML document stored in SQL Server 2008 R2 with XML datatype
You can try something like this:
SELECT
Barcode = Container.value('(BARCODE)[1]', 'int'),
CategoryID = Container.value('(CATEGORYID)[1]', 'int'),
Name = Container.value('(NAME)[1]', 'varchar(50)'),
GWT = Container.value('(GWT)[1]', 'decimal(10, 3)'),
NWT = Container.value('(NWT)[1]', 'decimal(10, 3)'),
LAM = Container.value('(LAM)[1]', 'decimal(10, 3)'),
QTY = Container.value('(QTY)[1]', 'int'),
Quantity = Container.value('(QUANTITY)[1]', 'int'),
Remarks = Container.value('(REMARKS)[1]', 'varchar(50)')
FROM
dbo.YourTableHere
CROSS APPLY
XmlColumnHere.nodes('/container/NewDataSet/Containers') AS T(Container)
WHERE
ID = 1
This will produce an output something like this (for your given sample data):
How to read XML column in SQL Server 2008?
with xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2014-03-29T09:41:23' as my)
select M.XMLData.value('(/my:myFields/my:field1/text())[1]', 'int') as field1,
M.XMLData.value('(/my:myFields/my:field2/text())[1]', 'int') as field2,
M.XMLData.value('(/my:myFields/my:field3/text())[1]', 'bit') as field3,
M.XMLData.value('(/my:myFields/my:FormName/text())[1]', 'datetime') as FormName,
(
select ','+R.X.value('text()[1]', 'nvarchar(max)')
from M.XMLData.nodes('/my:myFields/my:Repeating') as R(X)
for xml path(''), type
).value('substring(text()[1], 2)', 'nvarchar(max)') as Repeating
from XMLMain as M
Result:
field1 field2 field3 FormName Repeating
----------- ----------- ------ ----------------------- -----------------------
1 2 1 2014-04-01 15:11:47.000 hi,hello,how are you?
How to read a part of an XML as an XML in SQL Server 2008 R2
You're close - just use .query('.')
for your second column:
SELECT
T.C.value('(../@id)','int') AS UserID,
T.C.query('.') AS DepartmentsXML
FROM
@XML.nodes('/Main/User/Departments[@isSingle="0"]') AS T(C)
This will return the XML fragment as selected by the .nodes()
call, as an XML.
Efficiently query a XML column in SQL table
You can try this, which is about half the cost of the query you posted. The advantage is that it does not have to convert XML data to SQL data, which lowers the overhead:
DECLARE @Address varchar(210) = UPPER('user entered text')
SELECT *
FROM tblXMLData
WHERE XMLColumn.exist('/Session/Entries/Entry[@DataItemId="Address.Line1" and contains(upper-case(.), sql:variable("@Address"))]') = 1
If you find that you run this type of queries a lot, you can also consider XML index
Related Topics
How to Concatenate Numbers and Strings to Format Numbers in T-Sql
Stored Procedure That Exports Data into CSV Files Only Exports to One File
How to Remove Time from Datetime
Ad Hoc Queries VS Stored Procedures VS Dynamic SQL
How to See All the Tables in an Hsqldb Database
Selecting Top N Rows for Each Group in a Table
Why Postgres Returns Unordered Data in Select Query, After Updation of Row
In SQL Server How to Pivot for Multiple Columns
Use Access SQL to Do a Grouped Ranking
Coldfusion - Variable Field Name When Looping Through Database Query Results
Performance of SQL Server 2005 Query
How to Convert Postgresql 9.4's JSONb Type to Float
How to List All Tables in a Schema in Oracle SQL
How to Select Every Row Where Column Value Is Not Distinct
Difference Between Full Join & Inner Join
How to Group by Date Time Column Without Taking Time into Consideration