parsing XML from an XML field in a table

[Lonedog]

Currently I have been able to successfully parse part of the information I am
attempting to retrieve from a local table, it has one xml field that I am
interrogating.
I would like to retrieve not only the filednames, but the values of those
field names.
Here is the current code that works.
SELECT xl.XmlLogId
,t.row.value('local-name(.)','nvarchar(50)')
FROM XmlLog xl
CROSS APPLY information.nodes('declare namespace
API="https://validsys.net/API"; //API:VALIDCustomerEnrollA/*') t(row)

however I can not include the for the field:
as in
SELECT xl.XmlLogId
,t.row.value('local-name(.)','nvarchar(50)') as fieldname
,t.row.value('.','nvarchar(50)') as value
FROM XmlLog xl
CROSS APPLY information.nodes('declare namespace
API="https://validsys.net/API"; //API:VALIDCustomerEnrollA/*') t(row)

I want to avoid using a cursor ,and would prefer to handle this in a manner
that allow me to process multiple rows from the XmlLog table at once.
Your help is appreciated...

[Martin Honnen]

Lonedog wrote:
> Currently I have been able to successfully parse part of the information I am
> attempting to retrieve from a local table, it has one xml field that I am
> interrogating.
> I would like to retrieve not only the filednames, but the values of those
> field names.
> Here is the current code that works.
> SELECT xl.XmlLogId
> ,t.row.value('local-name(.)','nvarchar(50)')
> FROM XmlLog xl
> CROSS APPLY information.nodes('declare namespace
> API="https://validsys.net/API"; //API:VALIDCustomerEnrollA/*') t(row)
>
> however I can not include the for the field:
> as in
> SELECT xl.XmlLogId
> ,t.row.value('local-name(.)','nvarchar(50)') as fieldname
> ,t.row.value('.','nvarchar(50)') as value
> FROM XmlLog xl
> CROSS APPLY information.nodes('declare namespace
> API="https://validsys.net/API"; //API:VALIDCustomerEnrollA/*') t(row)

Do you get an error? Which one?
Or does the value method not give you the result you are looking for?
Maybe you want to use query('.') instead to return the XML?
It is not clear what exactly you want to achieve.

--

Martin Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/

[Lonedog]

First thanks for you quick reply!

Yes I do get an error,
Msg 9314, Level 16, State 1, Line 3
XQuery [XmlLog.Information.value()]: Cannot implicitly atomize or apply
'fn:data()' to complex content elements, found type 'xs:anyType' within
inferred type 'element({https://validsys.net/API}:userName,xs:string) |
element({https://validsys.net/API}:password,xs:string) | element.....

I want to query the xml data directly from the table without using an
interim variable, with the output being a two column table (that i could
pivot later)
as an example:

fieldname value
----------- ------
userName BLAH
password do3oo4odpaspooaemefoe78173884

[Martin Honnen]

Lonedog wrote:

> Yes I do get an error,
> Msg 9314, Level 16, State 1, Line 3
> XQuery [XmlLog.Information.value()]: Cannot implicitly atomize or apply
> 'fn:data()' to complex content elements, found type 'xs:anyType' within
> inferred type 'element({https://validsys.net/API}:userName,xs:string) |
> element({https://validsys.net/API}:password,xs:string) | element.....
>
> I want to query the xml data directly from the table without using an
> interim variable, with the output being a two column table (that i could
> pivot later)
> as an example:
>
> fieldname value
> ----------- ------
> userName BLAH
> password do3oo4odpaspooaemefoe78173884

That sounds as if the elements you are mapping to rows here with the
nodes function

>>> CROSS APPLY information.nodes('declare namespace
>>> API="https://validsys.net/API"; //API:VALIDCustomerEnrollA/*') t(row)

have child elements. Your approach would only work if you go down to
elements not having any child elements e.g.
information.nodes('//*[not(*)')

You might want to show a sample of the XML you have if the above does
not do what you want.

[Lonedog]

what's strange is the field name works correctly (but I am a newbie in this
area...)
However here is a sample of the xml stored in the table:

<?xml version="1.0" encoding="utf-8" ?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<VALIDCustomerEnrollA xmlns="https://validsys.net/API">
<userName>BLAH</userName>
<password>do3oo4odpaspooaemefoe78173884</password>
<requestID>82874</requestID>
<locationID>520</locationID>
<firstName>Tobe</firstName>
<middleName />
<lastName>Fr Cain</lastName>
<phoneWork />
<IDtype>2</IDtype>
<IDNumber>f373a4109624b183105626b825398bfe4cce666f20f94714f62bdc9d480adaaf</IDNumber>
<IDlocationDescription>Driver License</IDlocationDescription>
<DOB>12/30/1987</DOB>
<SSN>8699bcb39b389c55043f4807b822a721</SSN>
<gender />
<custAccountNumber />
<passCode />
<authenticationType>1</authenticationType>
<securityQuestionID>1</securityQuestionID>
<securityQuestionsAnswer>456272fddef1a00fee12d45a4a81f12e</securityQuestionsAnswer>
<institutionMember>0</institutionMember>
<cardSwipped>0</cardSwipped>
<bioSize />
</VALIDCustomerEnrollA>
</soap:Body>
</soap:Envelope>

Thanks! for the help

[Lonedog]

Discovered the problem, it has a problem with fields that are null (no data)
and shortened, as an example
<passCode />
instead of
<passCode> </passcode>

Thanks! Now I will troubleshoot this variation...