Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
Thanks
Saihi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCd ResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1 data2
(0 row(s) affected)
Thanks
Sai
"Chandra" wrote:
> hi Sai
> what is the message that you see.
> best Regards,
> Chandra
> http://www.SQLResource.com/
> http://chanduas.blogspot.com/
> ---
> *** Sent via Developersdex http://www.examnotes.net ***
>|||It's because ResultData is now in the tempuri namespace and you've added a
node to the path that is now missing in your OPENXML statement. Change your
code to:
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:t="http://tempuri.org/" />'
SELECT * FROM OPENXML(@.idoc,
'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
WITH
(
dat1 varchar(255) 't:dat1',
data2 varchar(255) 't:data2'
)
EXEC sp_xml_removedocument @.idoc
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Sai" <Sai@.discussions.microsoft.com> wrote in message
news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
Thanks
Sai|||Graeme
Thanks a lot,that was a great solution.
I was trying for it for long time :)
I greately appriciate your time and help.
Sai
"Graeme Malcolm" wrote:
> It's because ResultData is now in the tempuri namespace and you've added a
> node to the path that is now missing in your OPENXML statement. Change you
r
> code to:
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:t="http://tempuri.org/" />'
> SELECT * FROM OPENXML(@.idoc,
> 'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
> WITH
> (
> dat1 varchar(255) 't:dat1',
> data2 varchar(255) 't:data2'
> )
> EXEC sp_xml_removedocument @.idoc
> Cheers,
> Graeme
>
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Sai" <Sai@.discussions.microsoft.com> wrote in message
> news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
> Hi
> I would appriciate if any one could help me the following situation.
> The following code is working fine
> ----
--
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> ----
--
> if I add another tag
> <Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
> after adding the above the code looks as follows
> ----
--
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> ----
--
> Thanks
> Sai
>
>
Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts
Saturday, February 25, 2012
OPENXML with Multiple Namespace
Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
hi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***
|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCdResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1data2
(0 row(s) affected)
Thanks
Sai
"Chandra" wrote:
> hi Sai
> what is the message that you see.
> best Regards,
> Chandra
> http://www.SQLResource.com/
> http://chanduas.blogspot.com/
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||It's because ResultData is now in the tempuri namespace and you've added a
node to the path that is now missing in your OPENXML statement. Change your
code to:
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:t="http://tempuri.org/" />'
SELECT * FROM OPENXML(@.idoc,
'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
WITH
(
dat1 varchar(255) 't:dat1',
data2 varchar(255) 't:data2'
)
EXEC sp_xml_removedocument @.idoc
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Sai" <Sai@.discussions.microsoft.com> wrote in message
news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
|||Graeme
Thanks a lot,that was a great solution.
I was trying for it for long time
I greately appriciate your time and help.
Sai
"Graeme Malcolm" wrote:
> It's because ResultData is now in the tempuri namespace and you've added a
> node to the path that is now missing in your OPENXML statement. Change your
> code to:
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:t="http://tempuri.org/" />'
> SELECT * FROM OPENXML(@.idoc,
> 'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
> WITH
> (
> dat1 varchar(255) 't:dat1',
> data2 varchar(255) 't:data2'
> )
> EXEC sp_xml_removedocument @.idoc
> Cheers,
> Graeme
>
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Sai" <Sai@.discussions.microsoft.com> wrote in message
> news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
> Hi
> I would appriciate if any one could help me the following situation.
> The following code is working fine
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> if I add another tag
> <Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
> after adding the above the code looks as follows
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> Thanks
> Sai
>
>
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
hi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***
|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCdResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1data2
(0 row(s) affected)
Thanks
Sai
"Chandra" wrote:
> hi Sai
> what is the message that you see.
> best Regards,
> Chandra
> http://www.SQLResource.com/
> http://chanduas.blogspot.com/
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||It's because ResultData is now in the tempuri namespace and you've added a
node to the path that is now missing in your OPENXML statement. Change your
code to:
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:t="http://tempuri.org/" />'
SELECT * FROM OPENXML(@.idoc,
'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
WITH
(
dat1 varchar(255) 't:dat1',
data2 varchar(255) 't:data2'
)
EXEC sp_xml_removedocument @.idoc
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Sai" <Sai@.discussions.microsoft.com> wrote in message
news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
|||Graeme
Thanks a lot,that was a great solution.
I was trying for it for long time
I greately appriciate your time and help.
Sai
"Graeme Malcolm" wrote:
> It's because ResultData is now in the tempuri namespace and you've added a
> node to the path that is now missing in your OPENXML statement. Change your
> code to:
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:t="http://tempuri.org/" />'
> SELECT * FROM OPENXML(@.idoc,
> 'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
> WITH
> (
> dat1 varchar(255) 't:dat1',
> data2 varchar(255) 't:data2'
> )
> EXEC sp_xml_removedocument @.idoc
> Cheers,
> Graeme
>
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Sai" <Sai@.discussions.microsoft.com> wrote in message
> news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
> Hi
> I would appriciate if any one could help me the following situation.
> The following code is working fine
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> if I add another tag
> <Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
> after adding the above the code looks as follows
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> Thanks
> Sai
>
>
Monday, February 20, 2012
OPENXML Parsing Error: Multiple colons are not allow
I'm receiving the above parsing error for this document. I am trying to create an export that can be sent to a sister program and read into their database (same schemas). If I can export the entire entity (I do this using a union with nulls), I should
also be able to import it into another's database and maintain integrity. This is my attempt at the export half of it. I need to use OPENXML to be able to read the document once I receive it. This one is in non-binary format. The one I intend to use i
s binary, but binary is hard to debug so it is in this format. I have no idea what I am doing.
Thanks for any help.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
--Sample XML document
SET @.doc ='
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="Tag" rs:number="1">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID" rs:autoincrement="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
<s:datatype dt:type="float" dt:maxLength="8" rs:precision="15" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="SLSMNID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="CusID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
</s:AttributeType>
<s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c15" rs:name="P!2!active" rs:number="16" rs:nullable="true">
<s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, "/>
<z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True"/>
<z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True" c16="394" c17="2759" c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
</rs:data>
</xml>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( QuoteID int '.',
QuoteID int '../@. QuoteID',
QuoteID int '../../@. QuoteID')
EXEC sp_xml_removedocument @.idoc
Regards,
Jamie
It is not multiple colons that are the problem but that your WITH clause
uses the same name for all the columns.
Try:
SELECT c1 as QuoteID, c2 as QuoteID, c3 as QuoteID
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( c1 int '.',
c2 int '../@.QuoteID',
c3 int '../../@.QuoteID')
Furthermore, you want your @.doc variable to be at least of size 4000. And
you probably want to use path expressions in the OpenXML that actually
extracts information from your XML.
I don't understand your comment about binary format. OpenXML requires XML
and not some binary format.
I hope now have a better idea of what you are doing :-)
HTH
Michael
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:4FFA87D1-077F-40AF-AE84-F2C1AA325BEE@.microsoft.com...
> I'm receiving the above parsing error for this document. I am trying to
> create an export that can be sent to a sister program and read into their
> database (same schemas). If I can export the entire entity (I do this
> using a union with nulls), I should also be able to import it into
> another's database and maintain integrity. This is my attempt at the
> export half of it. I need to use OPENXML to be able to read the document
> once I receive it. This one is in non-binary format. The one I intend to
> use is binary, but binary is hard to debug so it is in this format. I
> have no idea what I am doing.
> Thanks for any help.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> --Sample XML document
> SET @.doc ='
> <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
> xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
> xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
> <s:Schema id="RowsetSchema">
> <s:ElementType name="row" content="eltOnly" rs:updatable="true">
> <s:AttributeType name="Tag" rs:number="1">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3"
> rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID"
> rs:autoincrement="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
> <s:datatype dt:type="float" dt:maxLength="8" rs:precision="15"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="SLSMNID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="CusID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
> </s:AttributeType>
> <s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
> </s:AttributeType>
> <s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15"
> rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c15" rs:name="P!2!active" rs:number="16"
> rs:nullable="true">
> <s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:extends type="rs:rowbase"/>
> </s:ElementType>
> </s:Schema>
> <rs:data>
> <z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286"
> c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1
> electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity
> bin, "/>
> <z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True"/>
> <z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True" c16="394" c17="2759"
> c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
> </rs:data>
> </xml>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/xml/QUOTEID')
> WITH ( QuoteID int '.',
> QuoteID int '../@. QuoteID',
> QuoteID int '../../@. QuoteID')
> EXEC sp_xml_removedocument @.idoc
> --
> Regards,
> Jamie
also be able to import it into another's database and maintain integrity. This is my attempt at the export half of it. I need to use OPENXML to be able to read the document once I receive it. This one is in non-binary format. The one I intend to use i
s binary, but binary is hard to debug so it is in this format. I have no idea what I am doing.
Thanks for any help.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
--Sample XML document
SET @.doc ='
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="Tag" rs:number="1">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID" rs:autoincrement="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
<s:datatype dt:type="float" dt:maxLength="8" rs:precision="15" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="SLSMNID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="CusID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
</s:AttributeType>
<s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c15" rs:name="P!2!active" rs:number="16" rs:nullable="true">
<s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, "/>
<z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True"/>
<z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True" c16="394" c17="2759" c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
</rs:data>
</xml>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( QuoteID int '.',
QuoteID int '../@. QuoteID',
QuoteID int '../../@. QuoteID')
EXEC sp_xml_removedocument @.idoc
Regards,
Jamie
It is not multiple colons that are the problem but that your WITH clause
uses the same name for all the columns.
Try:
SELECT c1 as QuoteID, c2 as QuoteID, c3 as QuoteID
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( c1 int '.',
c2 int '../@.QuoteID',
c3 int '../../@.QuoteID')
Furthermore, you want your @.doc variable to be at least of size 4000. And
you probably want to use path expressions in the OpenXML that actually
extracts information from your XML.
I don't understand your comment about binary format. OpenXML requires XML
and not some binary format.
I hope now have a better idea of what you are doing :-)
HTH
Michael
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:4FFA87D1-077F-40AF-AE84-F2C1AA325BEE@.microsoft.com...
> I'm receiving the above parsing error for this document. I am trying to
> create an export that can be sent to a sister program and read into their
> database (same schemas). If I can export the entire entity (I do this
> using a union with nulls), I should also be able to import it into
> another's database and maintain integrity. This is my attempt at the
> export half of it. I need to use OPENXML to be able to read the document
> once I receive it. This one is in non-binary format. The one I intend to
> use is binary, but binary is hard to debug so it is in this format. I
> have no idea what I am doing.
> Thanks for any help.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> --Sample XML document
> SET @.doc ='
> <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
> xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
> xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
> <s:Schema id="RowsetSchema">
> <s:ElementType name="row" content="eltOnly" rs:updatable="true">
> <s:AttributeType name="Tag" rs:number="1">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3"
> rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID"
> rs:autoincrement="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
> <s:datatype dt:type="float" dt:maxLength="8" rs:precision="15"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="SLSMNID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="CusID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
> </s:AttributeType>
> <s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
> </s:AttributeType>
> <s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15"
> rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c15" rs:name="P!2!active" rs:number="16"
> rs:nullable="true">
> <s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:extends type="rs:rowbase"/>
> </s:ElementType>
> </s:Schema>
> <rs:data>
> <z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286"
> c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1
> electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity
> bin, "/>
> <z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True"/>
> <z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True" c16="394" c17="2759"
> c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
> </rs:data>
> </xml>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/xml/QUOTEID')
> WITH ( QuoteID int '.',
> QuoteID int '../@. QuoteID',
> QuoteID int '../../@. QuoteID')
> EXEC sp_xml_removedocument @.idoc
> --
> Regards,
> Jamie
OpenXML multiple namespaces
Hi
I've been banging my head against this one for a while. There are examples around but I can't seem to find anything that works (or find an explanation).
The problem: The xml document uses an imported common schema to reference common tags/objects (xmlns:cmn) but I can't seem to get openXML working with multiple namespaces. If I remove the primary namespace (emboldened in red) the SELECT returns as expected. Editing the xml document is not an option.
Here's (a simplified version) of my code...
---------------------
DECLARE @.idoc INT
DECLARE @.doc varchar (8000)
SET @.doc ='
<?xml version="1.0" encoding="UTF-8"?>
<LHA_DATA xmlns="http://www.trs.co.uk/schema/DataShare.xsd" xmlns:cmn="http://www.trs.co.uk/schema/Common_ed.xsd">
<Manifest>
<cmn:senderRecipient>
<cmn:LACode>A0000</cmn:LACode>
<cmn:OfficeCode>0</cmn:OfficeCode>
</cmn:senderRecipient>
</Manifest>
</LHA_DATA>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc, '<LHA_DATA xmlns:cmn="xmlns="http://www.trs.co.uk/schema/DataShare.xsd" http://www.trs.co.uk/schema/Common_ed.xsd" />'
SELECT *
FROM OPENXML (@.idoc, 'LHA_DATA', 2)
WITH (LACode varchar(10) '//cmn:LACode',
OfficeCode varchar(10) '//cmn:OfficeCode')
EXEC sp_xml_removedocument @.idoc
----------------------
Any help greatly received!!!!Hi
My continued searching eventually proved fruitful :D . Anyone who stumbles on this with the same problem may find the following link useful.
http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic45014.aspx
Cheers
JW
I've been banging my head against this one for a while. There are examples around but I can't seem to find anything that works (or find an explanation).
The problem: The xml document uses an imported common schema to reference common tags/objects (xmlns:cmn) but I can't seem to get openXML working with multiple namespaces. If I remove the primary namespace (emboldened in red) the SELECT returns as expected. Editing the xml document is not an option.
Here's (a simplified version) of my code...
---------------------
DECLARE @.idoc INT
DECLARE @.doc varchar (8000)
SET @.doc ='
<?xml version="1.0" encoding="UTF-8"?>
<LHA_DATA xmlns="http://www.trs.co.uk/schema/DataShare.xsd" xmlns:cmn="http://www.trs.co.uk/schema/Common_ed.xsd">
<Manifest>
<cmn:senderRecipient>
<cmn:LACode>A0000</cmn:LACode>
<cmn:OfficeCode>0</cmn:OfficeCode>
</cmn:senderRecipient>
</Manifest>
</LHA_DATA>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc, '<LHA_DATA xmlns:cmn="xmlns="http://www.trs.co.uk/schema/DataShare.xsd" http://www.trs.co.uk/schema/Common_ed.xsd" />'
SELECT *
FROM OPENXML (@.idoc, 'LHA_DATA', 2)
WITH (LACode varchar(10) '//cmn:LACode',
OfficeCode varchar(10) '//cmn:OfficeCode')
EXEC sp_xml_removedocument @.idoc
----------------------
Any help greatly received!!!!Hi
My continued searching eventually proved fruitful :D . Anyone who stumbles on this with the same problem may find the following link useful.
http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic45014.aspx
Cheers
JW
Subscribe to:
Posts (Atom)