Problem with XQuery...

Hi all,

I've a little problem to read from a XML string using T-Sql, someone can help me please?
I use this XML string:
DECLARE @.x xml
SET @.x = '
<Envelope xmlns="urn:aerospace: dataschema: DocumentBusinessInformationEntitySchemaModule" xmlns:asram="urn: aerospaceataschema: ReusableAggregateBusinessInformationEntitySchemaModule">
<ROOT>
<asram:a>111</asram:a>
</ROOT>
</Envelope>
'

And this select :
SELECT @.x.query('/Envelope/ROOT/asram:a/text()') AS LastResult

Whow I can get de “111”?

Tks

In your query you need to make sure to declare the namespaces:

Code Snippet

DECLARE @.x xml;

SET @.x = '

<Envelope xmlns="urn:aerospace: dataschema: DocumentBusinessInformationEntitySchemaModule" xmlns:asram="urn: aerospace dataschema: ReusableAggregateBusinessInformationEntitySchemaModule">

<ROOT>

<asram:a>111</asram:a>

</ROOT>

</Envelope>;

'

SELECT @.x.query('

declare default element namespace "urn:aerospace: dataschema: DocumentBusinessInformationEntitySchemaModule";

declare namespace asram="urn: aerospace dataschema: ReusableAggregateBusinessInformationEntitySchemaModule";

/Envelope/ROOT/asram:a/text()

') AS LastResult;

|||Thank, that works,

But if i change the <Envelope> like that (
<Envelope xmlns="urn:aerospace: dataschema: documentBusinessInformationEntitySchemaModule" xmlns:asram="urn:aerospace: dataschema:ReusableAggregateBusinessInformationEntitySchemaModule" xmlns:ccts="urn:unece:uncefact: dataschema: draft:CoreComponentTypesSchemaModule:0:3:4" xmlns:udt="urn:unece:uncefact: dataschema: draft:UnqualifiedDataTypesSchemaModule:0:3:4" xmlns:mime="urn:unece:uncefact: dataschema: draft:codeList:MIME:MIME_Media_Types:2003::IANA" xmlns:rec20="urn:unece:uncefact: dataschema: draft:codeList:Rec20:Codes_for_Units_of_Measure:2001:6:UNECE" xmlns:iso4217="urn:unece:uncefact: dataschema: draft:identifierData:ISO_4217:Currency_Codes:2001:5:ISO" xmlns:iso639="urn:unece:uncefact: dataschema: draft:identifierData:ISO_639:Language_Code:1988:3:ISO" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsichemaLocation="urn:aerospace: dataschema: documentBusinessInformationEntitySchemaModule
\\Srci-gestion\SRCI\SRCI_Projets\Associations\CAPEMP~1\Norme_CapEmploi\R1.13\CapEmploi_Envelope_BIE_R1.13.xsd">
)
how I can get again the “111”|||

The only change seems to be in the namespac URNs so you need to adapt the XQuery namespace declarations as in this example:

Code Snippet

DECLARE @.x xml;

SET @.x = '

<Envelope xmlns="urn:aerospace: dataschema: documentBusinessInformationEntitySchemaModule" xmlns:asram="urn:aerospace: dataschema:ReusableAggregateBusinessInformationEntitySchemaModule" xmlns:ccts="urn:unece:uncefact: dataschema: draft:CoreComponentTypesSchemaModule:0:3:4" xmlns:udt="urn:unece:uncefact: dataschema: draft:UnqualifiedDataTypesSchemaModule:0:3:4" xmlns:mime="urn:unece:uncefact: dataschema: draft:codeList:MIME:MIME_Media_Types:2003::IANA" xmlns:rec20="urn:unece:uncefact: dataschema: draft:codeList:Rec20:Codes_for_Units_of_Measure:2001:6:UNECE" xmlns:iso4217="urn:unece:uncefact: dataschema: draft:identifierData:ISO_4217:Currency_Codes:2001:5:ISO" xmlns:iso639="urn:unece:uncefact: dataschema: draft:identifierData:ISO_639:Language_Code:1988:3:ISO" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:aerospace: dataschema: documentBusinessInformationEntitySchemaModule\\Srci-gestion\SRCI\SRCI_Projets\Associations\CAPEMP~1\Norme_CapEmploi\R1.13\CapEmploi_Envelope_BIE_R1.13.xsd">

<ROOT>

<asram:a>111</asram:a>

</ROOT>

</Envelope>;

'

SELECT @.x.query('

declare default element namespace "urn:aerospace: dataschema: documentBusinessInformationEntitySchemaModule";

declare namespace asram="urn:aerospace: dataschema:ReusableAggregateBusinessInformationEntitySchemaModule";

/Envelope/ROOT/asram:a/text()

') AS LastResult;

|||It works, thanks a lot.
(Merci beaucoup)