February 25, 2011 at 2:59 am
<?xml version="1.0" encoding="utf-16"?>
<Report
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://tempuri.org/ReportParameters.xsd">
<Fields>
<Field FieldStub="fa4b70e0-1e31-4412-b52b-5c018d721aa5" />
<Field FieldStub="98c179ac-7c81-41d9-8d06-a56bdfe4e97a" />
<Field FieldStub="c0ec9cc0-60eb-40c5-96f7-dd89b0b972ea" />
<Field FieldStub="72d87af7-a1bd-44e5-87f7-b894120352ce" />
<Field FieldStub="5f9a0b24-88d4-45bc-a9d3-13f3c286bba0" />
<Field FieldStub="9e411d25-719c-4b25-8d3c-84578e7c8bb3" AggregateTypeIds="1,2,3,4" />
<Field FieldStub="4e949544-fc5d-4332-aa4a-8656af9b37c6" />
<Field FieldStub="8a1b2d68-ea3e-4d8f-8ebd-1527bf855df2" />
<Field FieldStub="4b44365a-4332-488b-b233-d3c343610616" />
<Field FieldStub="8220b0a0-58a2-42e2-853d-6cac3ec0e66c" />
</Fields>
<Filters SearchType="AND">
<Filter FieldStub="4e949544-fc5d-4332-aa4a-8656af9b37c6" OpId="1" FieldValue="Yes" />
</Filters>
</Report>
i need to count number of "FieldStub" attributes ? i am not good in xquery.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 25, 2011 at 3:42 am
DECLARE @XML XML
;
SET @XML =
N'<?xml version="1.0" encoding="utf-16"?>
<Report
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://tempuri.org/ReportParameters.xsd">
<Fields>
<Field FieldStub="fa4b70e0-1e31-4412-b52b-5c018d721aa5" />
<Field FieldStub="98c179ac-7c81-41d9-8d06-a56bdfe4e97a" />
<Field FieldStub="c0ec9cc0-60eb-40c5-96f7-dd89b0b972ea" />
<Field FieldStub="72d87af7-a1bd-44e5-87f7-b894120352ce" />
<Field FieldStub="5f9a0b24-88d4-45bc-a9d3-13f3c286bba0" />
<Field FieldStub="9e411d25-719c-4b25-8d3c-84578e7c8bb3" AggregateTypeIds="1,2,3,4" />
<Field FieldStub="4e949544-fc5d-4332-aa4a-8656af9b37c6" />
<Field FieldStub="8a1b2d68-ea3e-4d8f-8ebd-1527bf855df2" />
<Field FieldStub="4b44365a-4332-488b-b233-d3c343610616" />
<Field FieldStub="8220b0a0-58a2-42e2-853d-6cac3ec0e66c" />
</Fields>
<Filters SearchType="AND">
<Filter FieldStub="4e949544-fc5d-4332-aa4a-8656af9b37c6" OpId="1" FieldValue="Yes" />
</Filters>
</Report>
';
;
WITH XMLNAMESPACES
(
DEFAULT 'http://tempuri.org/ReportParameters.xsd'
)
SELECT @XML.value('fn:count(/Report/Fields/Field/@FieldStub)', 'int')
;
February 25, 2011 at 4:25 am
SQLkiwi (2/25/2011)
DECLARE @XML XML
;
SET @XML =
N'<?xml version="1.0" encoding="utf-16"?>
<Report
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://tempuri.org/ReportParameters.xsd">
<Fields>
<Field FieldStub="fa4b70e0-1e31-4412-b52b-5c018d721aa5" />
<Field FieldStub="98c179ac-7c81-41d9-8d06-a56bdfe4e97a" />
<Field FieldStub="c0ec9cc0-60eb-40c5-96f7-dd89b0b972ea" />
<Field FieldStub="72d87af7-a1bd-44e5-87f7-b894120352ce" />
<Field FieldStub="5f9a0b24-88d4-45bc-a9d3-13f3c286bba0" />
<Field FieldStub="9e411d25-719c-4b25-8d3c-84578e7c8bb3" AggregateTypeIds="1,2,3,4" />
<Field FieldStub="4e949544-fc5d-4332-aa4a-8656af9b37c6" />
<Field FieldStub="8a1b2d68-ea3e-4d8f-8ebd-1527bf855df2" />
<Field FieldStub="4b44365a-4332-488b-b233-d3c343610616" />
<Field FieldStub="8220b0a0-58a2-42e2-853d-6cac3ec0e66c" />
</Fields>
<Filters SearchType="AND">
<Filter FieldStub="4e949544-fc5d-4332-aa4a-8656af9b37c6" OpId="1" FieldValue="Yes" />
</Filters>
</Report>
';
;
WITH XMLNAMESPACES
(
DEFAULT 'http://tempuri.org/ReportParameters.xsd'
)
SELECT @XML.value('fn:count(/Report/Fields/Field/@FieldStub)', 'int')
;
Thanks Paul
another question , here i posted the XMl string but
if this is a column of a table then what would be the query.
WITH XMLNAMESPACES
(
DEFAULT 'http://tempuri.org/ReportParameters.xsd'
)
SELECT rpt_param_xml.value('fn:count(/Report/Fields/Field/@FieldStub)', 'int')
FROM custom_report
this query is not working.
"rpt_param_xml" column has XMl data
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply