davidsalazar01 (2/12/2013)

---

I'm getting the following error when executing the following SQL:

SELECT ParamValues.XMLProfile_TXT.value('.','VARCHAR(MAX)') AS DID

FROM Payment_Method_T (nolock)

OUTER APPLY XMLProfile_TXT.nodes('/div/ul/li/did') ParamValues(XMLProfile_TXT);

Msg 9506, Level 16, State 1, Line 1

The XMLDT method 'nodes'...

From the sample data provided by Lowell: -

-- SAMPLE DATA

IF object_id('tempdb..#yourTable') IS NOT NULL

BEGIN

DROP TABLE #yourTable;

END;

SELECT id, CAST(XMLProfile_TXT AS XML) AS XMLProfile_TXT

INTO #yourTable

FROM (VALUES(1, '<div class="MainMenu"><ul><li><did>Home</did></li><li><did>Tags</did></li><li><did>Articles</did></li><li><did>Editorials</did></li><li><did>Stairways</did></li>

<li><did>Forums</did></li><li><did>Scripts</did></li><li><did>Videos</did></li><li><did>Blogs</did></li><li><did>QotD</did></li>

<li><did...

davidsalazar01 (2/11/2013)

---

Hello DBA's/Developers,

I'm trying to select XML data from one tag that is stored in an TEXT data type and can't seem to return the data I need.

e.g. XMLProfile_TXT field...

GordonLiddy (2/8/2013)

---

Thank you very much for your answers, guys!

@Cadavre: What do you mean when you say "key that allows to see the start and end.."?

The table with the odd design...

That looks suspiciously like white spaces. . . could you try this...

mssqlsrv (2/7/2013)

---

i need drive name & disk size in MB. & for that i need to convert it from varchar to int.

your query is working but i need values in...

It works here. . .

What is the result of this: -

DECLARE @DriveSpace AS TABLE(DriveLetter CHAR(1), FreeSpace VARCHAR(10));

DECLARE @DriveTemp AS TABLE(OutputInfo VARCHAR(MAX));

INSERT INTO @DriveSpace

EXEC master.dbo.xp_fixeddrives;

DECLARE curDriveLetters CURSOR FOR SELECT driveletter FROM...

I'm going to hope that your "odd" table has a key that allows you to see the start and end of each record, otherwise it is a disaster waiting to...

The simple answer is this: -

SELECT Drive, CAST(SUBSTRING(info, 32, 30) AS BIGINT) / 1024 / 1024

FROM @Drives

WHERE INFO LIKE 'Total # of bytes%'

ORDER BY drive;

Be aware that you are losing...

42. 😛

Hello and welcome to SSC,

If you could knock up some sample data and DDL scripts, then your expected results based on the sample data provided that would be extremely useful...

Based on your sample data: -

IF object_id('tempdb..#yourTable') IS NOT NULL

BEGIN

DROP TABLE #yourTable;

END;

SELECT id, CAST(ItemId AS XML) AS ItemId

INTO #yourTable

FROM (VALUES(1, '<itemids><itemid>259</itemid><itemid>489</itemid></itemids>'),

...

Something like this?

--== SAMPLE DATA ==--

DECLARE @XML XML = '<YourXML><TheData value="3.100000000000e+003" /></YourXML>';

--== VIEW SAMPLE DATA==--

SELECT @XML;

/* Looks like this: -

<YourXML>

<TheData value="3.100000000000e+003" />

</YourXML>

*/

--==Convert from scientific notation==--

SELECT Data.value('(@value)[1]', 'REAL')

FROM @XML.nodes('./YourXML/TheData')...

MyDoggieJessie (1/31/2013)

---

Won't this work (slightly different than what's posted)? - can't say I've ever noticed a count not equaling a select COUNT on a table directlySELECT ps.row_count

FROM sys.indexes AS...

Lowell (1/31/2013)

---

i thought you had to get the actual counts from sys.indexes; since the index must have a heap/PK index for every table, teh row counts are materialized exactly correct...