OPENXML Namespace

I am new to using XML. I have been given the following structure and
cannot figure out how to read the elements. I can get the edge table
but not the data in the format I am looking for. I believe there
should be 4 elements:
service_request_num, activity_code, outcome_code, details
I've been beating my head against the wall and the flat spots are
starting to show!
Can someone help?
TIA,
IDriveFast
DECLARE @.idoc int
DECLARE @.doc varchar(8000)
SET @.doc =
'<?xml version="1.0" encoding="UTF-8"?>
<aaa-msg
xmlns="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb">
<aaa:header>
<aaa:eid/>
<aaa:reference_id>1234</aaa:reference_id>
<aaa:event_date>JAN 10, 2003 02:07:21
PM</aaa:event_date>
<aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
<aaa:event_type_code>REQUEST</aaa:event_type_code>
<aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
<aaa:source_code>SIDEWALK</aaa:source_code>
<aaa:target_code>PRIMAPP</aaa:target_code>
<aaa:status_code />
<aaa:details/>
</aaa:header>
<aaa:body>
<bbb:document>
<bbb:header>
<bbb:system>bbb</bbb:system>
<bbb:doc_name>NA</bbb:doc_name>
<bbb:version>3.9.0</bbb:version>
<bbb:dml_event/>
<bbb:comment/>
</bbb:header>
<bbb:service_request>
<bbb:service_request_num>
<bbb:value>03-00002269</bbb:value>
</bbb:service_request_num>
<bbb:sr_activities>
<bbb:sr_activity>
<bbb:activity_code>
<bbb:value>ACTIVIT3</bbb:value>
</bbb:activity_code>
<bbb:outcome_code>
<bbb:value>LINKSR</bbb:value>
</bbb:outcome_code>
<bbb:details>
<bbb:value>Details go here</bbb:value>
</bbb:details>
</bbb:sr_activity>
</bbb:sr_activities>
</bbb:service_request>
</bbb:document>
</aaa:body>
</aaa-msg>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
SELECT *
FROM OPENXML (@.idoc, '/',2)
Tia,
There are three things that you need to do to get this to work.
1) Define all the namespaces that are required.
2) Select a xpath that is pass to the OPENXML function to filter your data.
3) Use the WITH clause to select the specific data you are interested in.
I've supplied two example queries that seem to meet your requirements. One
assume that only one activity occurs per request and one that assume there
can be multiple activities per request.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
'<aaa xmlns="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
-- service request based
SELECT *
FROM
OPENXML (@.idoc, '/aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
WITH
(
service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
activity_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
outcome_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
details nvarchar(4000)
'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
)
-- activity based
SELECT *
FROM
OPENXML (@.idoc,
'/aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bbb:sr_activity',2)
WITH
(
service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value',
activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
details nvarchar(4000) 'bbb:details/bbb:value'
)
EXEC sp_xml_removedocument @.idoc
"I Drive Fast" wrote:

> I am new to using XML. I have been given the following structure and
> cannot figure out how to read the elements. I can get the edge table
> but not the data in the format I am looking for. I believe there
> should be 4 elements:
> service_request_num, activity_code, outcome_code, details
> I've been beating my head against the wall and the flat spots are
> starting to show!
> Can someone help?
> TIA,
> IDriveFast
> DECLARE @.idoc int
> DECLARE @.doc varchar(8000)
> SET @.doc =
> '<?xml version="1.0" encoding="UTF-8"?>
> <aaa-msg
> xmlns="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb">
> <aaa:header>
> <aaa:eid/>
> <aaa:reference_id>1234</aaa:reference_id>
> <aaa:event_date>JAN 10, 2003 02:07:21
> PM</aaa:event_date>
> <aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
> <aaa:event_type_code>REQUEST</aaa:event_type_code>
> <aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
> <aaa:source_code>SIDEWALK</aaa:source_code>
> <aaa:target_code>PRIMAPP</aaa:target_code>
> <aaa:status_code />
> <aaa:details/>
> </aaa:header>
> <aaa:body>
> <bbb:document>
> <bbb:header>
> <bbb:system>bbb</bbb:system>
> <bbb:doc_name>NA</bbb:doc_name>
> <bbb:version>3.9.0</bbb:version>
> <bbb:dml_event/>
> <bbb:comment/>
> </bbb:header>
> <bbb:service_request>
> <bbb:service_request_num>
> <bbb:value>03-00002269</bbb:value>
> </bbb:service_request_num>
> <bbb:sr_activities>
> <bbb:sr_activity>
> <bbb:activity_code>
> <bbb:value>ACTIVIT3</bbb:value>
> </bbb:activity_code>
> <bbb:outcome_code>
> <bbb:value>LINKSR</bbb:value>
> </bbb:outcome_code>
> <bbb:details>
> <bbb:value>Details go here</bbb:value>
> </bbb:details>
> </bbb:sr_activity>
> </bbb:sr_activities>
> </bbb:service_request>
> </bbb:document>
> </aaa:body>
> </aaa-msg>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
> SELECT *
> FROM OPENXML (@.idoc, '/',2)
>
|||Thanks for both answers!
On Tue, 31 Jan 2006 12:19:30 -0800, "Galex Yen [MSFT]"
<galexy@.online.microsoft.com> wrote:
[vbcol=seagreen]
>Tia,
>There are three things that you need to do to get this to work.
>1) Define all the namespaces that are required.
>2) Select a xpath that is pass to the OPENXML function to filter your data.
>3) Use the WITH clause to select the specific data you are interested in.
>I've supplied two example queries that seem to meet your requirements. One
>assume that only one activity occurs per request and one that assume there
>can be multiple activities per request.
>EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
> '<aaa xmlns="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
>-- service request based
>SELECT *
>FROM
>OPENXML (@.idoc, '/aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
>WITH
>(
>service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
>activity_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
>outcome_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
>details nvarchar(4000)
>'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
>)
>-- activity based
>SELECT *
>FROM
>OPENXML (@.idoc,
>'/aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bbb:sr_activity',2)
>WITH
>(
>service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value',
>activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
>outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
>details nvarchar(4000) 'bbb:details/bbb:value'
>)
>EXEC sp_xml_removedocument @.idoc
>"I Drive Fast" wrote: