OpenXml sub elements

Hi All,
I,m shredding this xml to view the data and ultimate goal is to insert
the data at a later stage but when I shred it with the syntax
mentioned below then I recieve only one record instead of 3 records
with different AnsOptions (last sub element).
Help!
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo />
<DVName>Home</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1.?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Answer>No</Answer>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
<Ansoption>
<Options>Time Waiting</Options>
</Ansoption>
<Ansoption>
<Options>Rude/Abrupt</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
--split --
SELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer', 2)
WITH
(DVName varchar (20) '../../../DVName',
DvcodeNo int '../../../DvcodeNo',
CustSurveyNo int '../../CustSurveyNo',
QuestionID int '../QuestionID',
QuestionDesc varchar(50) '../QuestionDesc',
AnswerID int,
Ansoption varchar (30))
EXEC sp_xml_removedocument @.docHandle
Results
Home 1 4 34 Q1.? 13 Unhelpful
Any Ideas ?, I need to finish this procedure as quick as possible,
Please adviseSELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30) 'Options')|||Thanks all sorted!
markc...@.hotmail.com wrote:
> SELECT *
> FROM
> -- Add OPENXML statement for ResponseDetail table
> OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
> Ansoption', 2)
> WITH
> (DVName varchar (20) '../../../../DVName',
> DvcodeNo int '../../../../DvcodeNo',
> CustSurveyNo int '../../../CustSurveyNo',
> QuestionID int '../../QuestionID',
> QuestionDesc varchar(50) '../../QuestionDesc',
> AnswerID int '../AnswerID',
> Ansoption varchar (30) 'Options')|||It Doesn't work again if I add more data to the XML :
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1. Was your call answered prompt and
courteously?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
SELECT *
FROM
-- Add OPENXML statement for SalesOrderDetail table INSERT
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @.docHandle

OpenXml sub elements

Hi All,
I,m shredding this xml to view the data and ultimate goal is to insert
the data at a later stage but when I shred it with the syntax
mentioned below then I recieve only one record instead of 3 records
with different AnsOptions (last sub element).
Help!
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo />
<DVName>Home</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1.?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Answer>No</Answer>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
<Ansoption>
<Options>Time Waiting</Options>
</Ansoption>
<Ansoption>
<Options>Rude/Abrupt</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
--split --
SELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer', 2)
WITH
(DVName varchar (20) '../../../DVName',
DvcodeNo int '../../../DvcodeNo',
CustSurveyNo int '../../CustSurveyNo',
QuestionID int '../QuestionID',
QuestionDesc varchar(50) '../QuestionDesc',
AnswerID int,
Ansoption varchar (30))
EXEC sp_xml_removedocument @.docHandle
Results
Home1434Q1.?13Unhelpful
Any Ideas ?, I need to finish this procedure as quick as possible,
Please advise
SELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30) 'Options')
|||Thanks all sorted!
markc...@.hotmail.com wrote:
> SELECT *
> FROM
> -- Add OPENXML statement for ResponseDetail table
> OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
> Ansoption', 2)
> WITH
> (DVName varchar (20) '../../../../DVName',
> DvcodeNo int '../../../../DvcodeNo',
> CustSurveyNo int '../../../CustSurveyNo',
> QuestionID int '../../QuestionID',
> QuestionDesc varchar(50) '../../QuestionDesc',
> AnswerID int '../AnswerID',
> Ansoption varchar (30) 'Options')
|||It Doesn't work again if I add more data to the XML :
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1. Was your call answered prompt and
courteously?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
SELECT *
FROM
-- Add OPENXML statement for SalesOrderDetail table INSERT
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @.docHandle

OPENXML question

I am using the OpenXml command and I have a question (XML posted below)...
I don't know how to get the values of certain elements if they columns
needed are not attributes to the XML...
For instance I can get the value of /attributes/items for the type attribute
but if I try to get the values of the /attributes/items/item elements it
returns null.
'<attributes>
<lot>E070484</lot>
<from-location type="dock">AA01</from-location>
<dest-location type="dock">AA05</dest-location>
<items type="yarn">
<item>QP35990</item>
<item>QP36134</item>
</items>
</attributes>'
JITry the following:
SELECT *
FROM OPENXML ( @.idoc, '/attributes/items/item',2 )
with
([Value] varchar(100) '.')
Q
"JI" wrote:

> I am using the OpenXml command and I have a question (XML posted below)...
> I don't know how to get the values of certain elements if they columns
> needed are not attributes to the XML...
> For instance I can get the value of /attributes/items for the type attribu
te
> but if I try to get the values of the /attributes/items/item elements it
> returns null.
> '<attributes>
> <lot>E070484</lot>
> <from-location type="dock">AA01</from-location>
> <dest-location type="dock">AA05</dest-location>
> <items type="yarn">
> <item>QP35990</item>
> <item>QP36134</item>
> </items>
> </attributes>'
>
> --
> JI|||Try,
use northwind
go
declare @.idoc int
declare @.doc varchar(1000)
set @.doc ='<attributes>
<lot>E070484</lot>
<from-location type="dock">AA01</from-location>
<dest-location type="dock">AA05</dest-location>
<items type="yarn">
<item>QP35990</item>
<item>QP36134</item>
</items>
</attributes>'
exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT
*
FROM
OPENXML (@.idoc, '/attributes/items/item')
with (item varchar(10) '/attributes/items/item')
EXEC sp_xml_removedocument @.idoc
"JI" wrote:

> I am using the OpenXml command and I have a question (XML posted below)...
> I don't know how to get the values of certain elements if they columns
> needed are not attributes to the XML...
> For instance I can get the value of /attributes/items for the type attribu
te
> but if I try to get the values of the /attributes/items/item elements it
> returns null.
> '<attributes>
> <lot>E070484</lot>
> <from-location type="dock">AA01</from-location>
> <dest-location type="dock">AA05</dest-location>
> <items type="yarn">
> <item>QP35990</item>
> <item>QP36134</item>
> </items>
> </attributes>'
>
> --
> JI

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:

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:aaa-msg
xmlns:aaa="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: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:aaa="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
-- service request based
SELECT *
FROM
OPENXML (@.idoc, '/aaa: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:aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bb
b: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:aaa-msg
> xmlns:aaa="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: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:
>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:aaa="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
>-- service request based
>SELECT *
>FROM
>OPENXML (@.idoc, '/aaa: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:aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/b
bb: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:
>