Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query XML data for blsnkd Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 9:17 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:48 PM
Points: 135, Visits: 286
Hi
I am try to see if there are any blanks in a node of a table that has xml data in one of the columns. The query I use is returning zero results. Any suggestions?
Select COUNT(*)from ENTITY
Where CONVERT(XML, Ent_root_xml, 0 ).value('(//UD_PQ_FLAG/node())[1]', 'VARCHAR(50)')= ''
Post #1566909
Posted Sunday, May 4, 2014 5:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 2,228, Visits: 6,033
Quite few ways of doing this, here is one


DECLARE @XML_STUFF TABLE
(
XML_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,XML_DATA XML NOT NULL
)

INSERT INTO @XML_STUFF (XML_DATA)
VALUES
(N'<ROOT>
<ELEMENT01>
<DETAIL>HAS STUFF</DETAIL>
</ELEMENT01>
</ROOT>')
,(N'<ROOT>
<ELEMENT01>
<!-- HAS NO STUFF -->
</ELEMENT01>
</ROOT>')
,(N'<ROOT>
<ELEMENT01>
<DETAIL>HAS STUFF</DETAIL>
</ELEMENT01>
</ROOT>');

SELECT
XS.XML_ID
,DET.AIL.value('.[1]','NVARCHAR(128)') AS DETAIL_TEXT
FROM @XML_STUFF XS
OUTER APPLY XS.XML_DATA.nodes('ROOT/ELEMENT01/DETAIL') AS DET(AIL);

Results
XML_ID      DETAIL_TEXT
----------- ------------
1 HAS STUFF
2 NULL
3 HAS STUFF
Post #1567330
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse