PLEASE HELP!!! XML/SOAP QUERY!!!!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PLEASE HELP!!! XML/SOAP QUERY!!!!!

Author

Topic

ics0jey
Starting Member

2 Posts

Posted - 2011-11-22 : 10:07:29

Hello

I have already posted this on StackOverflow and no-one has replied! I need to extract data from an XML document which uses SOAP. I have tried all of the standard things I can find and it just keeps returning a NULL value, any ideas???

I am really new to XMl and have been tasked with extracting some values from an XML column.

I have read LOTS of example of how to do this on the internet and have tried several different methods. Whilst I can get these to work, they only return null values so clearly something isn't going right. The only difference I can see is that in our XML code they use SOAP. A previous developer wrote the code and so I cannot ask them.

These are the methods I have tried:

SELECT
settings.value('item[1]/_value[1]', 'int') as PrinterId
FROM dbo.usersettings
where userid = 2156

----------------------------------------------------------------------

create table XMLPrinters (PrinterList xml)
insert XMLPrinters select settings
from dbo.UserSettings
where userid = 2156

select
r.p.value ('item[1]/_key[1]', 'nvarchar (50)') as PrinterType,
r.p.value ('item[1]/_value[1]', 'int') as PrinterId
from XMLPrinters
cross apply printerlist.nodes ('//item') r(p)

As I say, I am very new to XML and don't come from a coding background so there probably is something I am missing, but the XML code doesn't seem to fit other formats I have seen on the web, and I guess this is probably due to the SOAP.

The code itself is 2496 lines long, but this is an extract from the beginning where I am trying to extract the values from:

<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<SOAP-ENC:Array SOAP-ENC:arrayType="xsd:anyType[5]">
<item xmlns:a2="http://schemas.microsoft.com/clr/ns/System.Collections" xsi:type="a2:DictionaryEntry">
<_key xsi:type="xsd:string">primaryprinter</_key>
<_value xsi:type="xsd:anyType" xsi:null="1" />
</item>

<item xmlns:a2="http://schemas.microsoft.com/clr/ns/System.Collections" xsi:type="a2:DictionaryEntry">
<_key xsi:type="xsd:string">secondaryprinter</_key>
<_value xsi:type="xsd:anyType" xsi:null="1" />
</item>

<item xmlns:a2="http://schemas.microsoft.com/clr/ns/System.Collections" si:type="a2:DictionaryEntry">
<_key xsi:type="xsd:string">i18mminstancelabel</_key>
<_value xsi:type="xsd:string">1056</_value>
</item>

Eventually what I need to do is to extract _value where _key = PrimaryPrinter and do this for each user.

Any help or ideas anyone has would be greatly received!

Regards

Julie

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-22 : 11:45:53

Since _key and _values are nodes under the item node, and since you navigated to item in the cross apply, you don't need item in the value function parameter. So change it to:


select 
r.p.value ('./_key[1]', 'nvarchar (50)') as PrinterType,
r.p.value ('./_value[1]', 'int') as PrinterId 
from XMLPrinters 
cross apply printerlist.nodes ('//item') r(p)

ics0jey
Starting Member

2 Posts

Posted - 2011-11-23 : 04:28:12

You Sunita are an ANGEL!!!! THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I had to change both data types to Nvarchar (max) but that worked beautifully!!!

I REALLY appreciate your help!!

Julie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources