node() method ... xmlns

[Will]

Sql Server 2005:

The following query works fine with the UPS .xml doc that follows, but only
if I remove the root element attributes. Do I need to somehow take the root
element xmlns declaration into account when writing this query? If so, what
is query syntax that will work with the root element as shown in the .xml
doc below?. Thanks in advance ...

SELECT Node.query('.')
FROM @xml.nodes('//Package') TempXML (Node)

<UPS_EBR_BILL
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.ups.com/XMLSchema/EBR/Billing/v1"
xsi:schemaLocation="http://www.ups.com/XMLSchema/EBR/Billing/v1
Billing_XML_Schema.xsd">

{body of .xml doc goes here}

</UPS_EBR_BILL>

[Martin Honnen]

Will wrote:

The query and nodes function take an XQuery expression (the version of
XQuery that Microsoft has implemented). XQuery contains XPath as a
subset so what you learned earlier about XPath for openxml applies here
as well, you will need to bind a prefix to the namespace and use that
prefix to qualify element names in your expression(s). Only how you bind
a prefix differs; one way is as follows:

SELECT Node.query('.')
FROM @xml.nodes('

declare namespace v1="http://www.ups.com/XMLSchema/EBR/Billing/v1";
//v1:Package') TempXML (Node)

a second way is as follows:

WITH XMLNAMESPACES ('http://www.ups.com/XMLSchema/EBR/Billing/v1' AS v1)

SELECT Node.query('.')
FROM @xml.nodes('

//v1:Package') TempXML (Node)

A third choice is specific to XQuery, you can declare a default element
namespace so that you don't need a prefix:

SELECT Node.query('.')
FROM @xml.nodes('

declare default element namespace
v1="http://www.ups.com/XMLSchema/EBR/Billing/v1";
//Package') TempXML (Node)

--

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

[Will]

Hi Martin,

I knew I must be running into xmlns recognition issue again, but could not
figure out syntax. Your examples below could not be clearer. Thank you. They
are exactly what I needed.

The first two examples ran perfectly. Only on the 3rd example I am getting
this error: "Msg 9317, Level 16, State 1, Line 6 XQuery [nodes()]: Syntax
error near 'declare namespace', expected string literal." I tried prefixing
//Package with //v1:Package, but still got error.

One final question:
In some ways, the node() method suits my needs even better than an Edge
table. What is best practice for manipulating return from node()? I could
easily apply string functions to get the data I need, rather than apply any
of sql server's other xml features. Are string functions a 'recognized way'
to manipulate the results of node()?

You have saved me a ton of time and effort in understanding tsql xml
features (not to mention xml itself). Can't thank you enough ...

[Martin Honnen]

Will wrote:

> The first two examples ran perfectly. Only on the 3rd example I am getting
> this error: "Msg 9317, Level 16, State 1, Line 6 XQuery [nodes()]: Syntax
> error near 'declare namespace', expected string literal." I tried prefixing
> //Package with //v1:Package, but still got error.

Yes, my bad, the proper syntax would be

SELECT Node.query('.')
FROM @xml.nodes('
declare default element namespace

"http://www.ups.com/XMLSchema/EBR/Billing/v1";
//Package') TempXML (Node)

> One final question:
> In some ways, the node() method suits my needs even better than an Edge
> table. What is best practice for manipulating return from node()? I could
> easily apply string functions to get the data I need, rather than apply any
> of sql server's other xml features. Are string functions a 'recognized way'
> to manipulate the results of node()?

I think a pattern used often is a combination of the 'nodes' and the
'value' method e.g.

SELECT
row.value('foo[1]', 'int') AS foo,
row.value('bar[1]', 'nvarchar(20)') AS bar
FROM @xml.nodes('root/row') AS T(row);

where you basically use the nodes function to map XML elements to a row
set and then the value function to 'cast' XML 'column' values to SQL types.

[Will]

Now the 3rd example works fine. Thanks again.

Have to monkey with the value method, but from what you write below, looks
straightforward and intuitive. I will re-post with any questions.

Sql Server really can be a one-stop shop for xml. That's what I am looking
for.

Best Regards,

Bill

[Will]

Hi again Martin,

I'm having trouble with this last step: I need to convert the return from
node() method into simple rows, columns (a simple table that looks like
regular, relational data).

To keep it simple, assume no xmlns - @x contains the xml doc and is datatype
xml -I only need to return one column.

The following code works fine, and returns one column with 745 rows, all
beginning with <Charge>, ending with </Charge>, and all children elements
appear between those two tags.

SELECT Node.query('.')
FROM @x.nodes('//Charge') TempXML (Node)

Now say I want to return a child node value that exists in the query return
above. I need to get the values that exist between the beginning/end tags
(not the attributes) for the NetAmount element. For example, I need to get,
in one column, the 3.45 from <NetAmount>3.45</NetAmount> and all the other
values that exist between those two tags in the entire .xml doc.

The following code returns 745 rows as expected, but all the values are
null. What am I doing wrong?
SELECT
row.value('NetAmount[1]', 'varchar(10)') AS NetAmount
FROM @x.nodes('//Charge') AS T(row);

Thanks for your help ...

Will

[Martin Honnen]

Will wrote:

> Now say I want to return a child node value that exists in the query return
> above. I need to get the values that exist between the beginning/end tags
> (not the attributes) for the NetAmount element. For example, I need to get,
> in one column, the 3.45 from <NetAmount>3.45</NetAmount> and all the other
> values that exist between those two tags in the entire .xml doc.
>
> The following code returns 745 rows as expected, but all the values are
> null. What am I doing wrong?
> SELECT
> row.value('NetAmount[1]', 'varchar(10)') AS NetAmount
> FROM @x.nodes('//Charge') AS T(row);

Show us the relevant XML (not all but a meaningful sample). Your query
would work if you have 'NetAmount' child elements for the the 'Charge'
elements e.g.

<root>
<Charge>
<NetAmount>3.45</NetAmount>
</Charge>
...
</root>

[Will]

Hi Martin<

Here is testing tsql I use. UPS .xml's are huge, so I cut out a section for
you to see - works ok on this end; problem still that NetAmount query
returning NULL's.

-- drop temp table if it exists
if object_id('tempdb..#t') is not null begin drop table #t end;

-- create temp table for testing
create table #t
(
col xml null
);

-- get xml doc into temp table for testing
insert into #t (col) values('<?xml version="1.0" encoding="ISO-8859-1" ?>
<UPS_EBR_BILL>
<Charge><ClassificationCode>FRT</ClassificationCode>
<ChargeInformation><DescriptionCode>001
</DescriptionCode><DescriptionOfCharges>Next Day Air
Commercial</DescriptionOfCharges><CurrencyCode>USD</CurrencyCode><IncentiveAmount>15.70</IncentiveAmount><NetAmount>8.85</NetAmount></ChargeInformation>
</Charge>
<Charge><ClassificationCode>FSC</ClassificationCode>
<ChargeInformation><DescriptionCode>FSC</DescriptionCode><DescriptionOfCharges>Fuel
Surcharge</DescriptionOfCharges><ChargedUnitQuantity>0000001</ChargedUnitQuantity><BasisCurrencyCode>USD</BasisCurrencyCode><BasisValue>25.00</BasisValue><CurrencyCode>USD</CurrencyCode><IncentiveAmount>0.50</IncentiveAmount><NetAmount>5.13</NetAmount></ChargeInformation>
</Charge>
<Charge><ClassificationCode>FRT</ClassificationCode>
<ChargeInformation><DescriptionCode></DescriptionCode><DescriptionOfCharges>Service
Charge</DescriptionOfCharges><CurrencyCode>USD</CurrencyCode><IncentiveAmount>5.90</IncentiveAmount><NetAmount>15.10</NetAmount></ChargeInformation>
</Charge>
</UPS_EBR_BILL>');

-- test node method to make sure it works
declare @x xml;
set @x = (select col from #t);
select node.query('.')
from @x.nodes('//Charge') tempXml (node);

-- test get NetAmount from from node() method return
declare @x xml;
set @x = (select col from #t);
select
row.value('NetAmount[1]', 'varchar(10)') as netAmount
from @x.nodes('//Charge') as T(row);

-- return from just previous query:
netAmount
NULL
NULL
NULL

[Martin Honnen]

Will wrote:

> insert into #t (col) values('<?xml version="1.0" encoding="ISO-8859-1" ?>
> <UPS_EBR_BILL>
> <Charge><ClassificationCode>FRT</ClassificationCode>
> <ChargeInformation><DescriptionCode>001
> </DescriptionCode><DescriptionOfCharges>Next Day Air
> Commercial</DescriptionOfCharges><CurrencyCode>USD</CurrencyCode><IncentiveAmount>15.70</IncentiveAmount><NetAmount>8.85</NetAmount></ChargeInformation>
> </Charge>
> <Charge><ClassificationCode>FSC</ClassificationCode>
> <ChargeInformation><DescriptionCode>FSC</DescriptionCode><DescriptionOfCharges>Fuel
> Surcharge</DescriptionOfCharges><ChargedUnitQuantity>0000001</ChargedUnitQuantity><BasisCurrencyCode>USD</BasisCurrencyCode><BasisValue>25.00</BasisValue><CurrencyCode>USD</CurrencyCode><IncentiveAmount>0.50</IncentiveAmount><NetAmount>5.13</NetAmount></ChargeInformation>
> </Charge>
> <Charge><ClassificationCode>FRT</ClassificationCode>
> <ChargeInformation><DescriptionCode></DescriptionCode><DescriptionOfCharges>Service
> Charge</DescriptionOfCharges><CurrencyCode>USD</CurrencyCode><IncentiveAmount>5.90</IncentiveAmount><NetAmount>15.10</NetAmount></ChargeInformation>
> </Charge>
> </UPS_EBR_BILL>');

> select
> row.value('NetAmount[1]', 'varchar(10)') as netAmount
> from @x.nodes('//Charge') as T(row);

Those NetAmount elements are grandchildren of the Charge element so you
need
row.value('(ChargeInformation/NetAmount)[1]', 'varchar(10)') AS
NetAmount

--

[Will]

Thank you, Martin. With that answer I have the info I need to add more
columns as well.
If I'm understanding this right, I wasn't paying attention to the value()
method 'singleton' requirement.

One more question regarding xmlns:
Is there a way to use modify() 'replace value of ...' to remove the xmlns
or change the string 'xmlns' to a new string like 'xmlnsX'
- i.e., any way to disable the need to deal with the xmlns declaration
so that queries are simpler, and also to eliminate possiblity of code
failing when the xmlns changes for .docs we import regularly?

[Martin Honnen]

Will wrote:
> Thank you, Martin. With that answer I have the info I need to add more
> columns as well.
> If I'm understanding this right, I wasn't paying attention to the value()
> method 'singleton' requirement.

Not really, you had the [1] at the end, but you did not have the right
XPath, your path 'NetAmount[1]' selected the first child element of that
name which did not exist. What exists is a grandchild of that name while
the child element is named ChargeInformation so to select the grandchild
you need the path '(ChargeInformation/NetAmount)[1]'.

> One more question regarding xmlns:
> Is there a way to use modify() 'replace value of ...' to remove the xmlns
> or change the string 'xmlns' to a new string like 'xmlnsX'
> - i.e., any way to disable the need to deal with the xmlns declaration
> so that queries are simpler, and also to eliminate possiblity of code
> failing when the xmlns changes for .docs we import regularly?

The xml data type supports some XQuery and in XQuery you can use e.g.
*:foo
to select elements with local name 'foo' in any namespace (i.e. the '*'
is a wild card used instead of a concrete prefix). That can help to
select elements solely based on their local name, ignoring any namespace.

[Will]

Hi Martin,

Ok ... that will work. I am also experimenting with getting .xml doc into
varchar(max) first so I can manipulate xmlns if I need to.

I believe I have enough to go on to tackle the expanded UPS .xml doc using
query, nodes, value methods and also 'cross apply' clause (which I need to
dig into a bit more).

Openxml() is simple, but it does use significant resources for larger docs.

Your answers are exceptionally clear and complete ... thank you again.

Will

[Will]

Hi Martin,

I am still missing one piece of the puzzle:

In the UPS .xml document, I need to go higher up the node tree to get the
TrackingNumber (great grand parent to NetAmount). Then I need to return
relational data that joins the TrackingNumber to the related NetAmounts.

Since the value method must return a singleton, what is the code that will
return relational data (in this case, TrackingNumber and related NetAmounts)
from the UPS .xml doc, given that I do not know how many <Charge> tags exist
between any given set of <TrackingNumber> tags, and I also do not know how
many <NetAmount> tags exist between any given set of <Charge tags>?

For example, I need to return the following relational data from the UPS
.xml segment below, but without knowing in advance how many NetAmount values
will be returned:

TrackingNumber NetAmount
1Z0000050343506999 7.04
1Z0000050343506999 4.05
1Z0000050343506999 0.50

<Package>
<TrackingNumber>1Z0000050343506999</TrackingNumber>
<ChargeDetails>
<Charge>
<ClassificationCode>FQR</ClassificationCode>
<ChargeInformation>
<DescriptionCode>007</DescriptionCode>
<DescriptionOfCharges>Ground</DescriptionOfCharges>
<CurrencyCode>USD</CurrencyCode>
<IncentiveAmount>5.96</IncentiveAmount>
<NetAmount>7.04</NetAmount>
</ChargeInformation>
</Charge>
<Charge>
<ClassificationCode>ACT</ClassificationCode>
<ChargeInformation>
<DescriptionCode>RPS</DescriptionCode>
<DescriptionOfCharges>RSurcharge</DescriptionOfCharges>
<ChargedUnitQuantity>000005</ChargedUnitQuantity>
<CurrencyCode>USD</CurrencyCode>
<IncentiveAmount>1.00</IncentiveAmount>
<NetAmount>4.05</NetAmount>
</ChargeInformation>
</Charge>
<Charge>
<ClassificationCode>FTY</ClassificationCode>
<ChargeInformation>
<DescriptionCode>FHJ</DescriptionCode>
<DescriptionOfCharges>Fuel Surcharge</DescriptionOfCharges>
<ChargedUnitQuantity>0005</ChargedUnitQuantity>
<BasisCurrencyCode>USD</BasisCurrencyCode>
<BasisValue>12.05</BasisValue>
<CurrencyCode>USD</CurrencyCode>
<IncentiveAmount>0.25</IncentiveAmount>
<NetAmount>0.50</NetAmount>
</ChargeInformation>
</Charge>
</ChargeDetails>
</Package>

[Martin Honnen]

Will wrote:

With XPath/XQuery
..
gives you the parent node
../..
the grandparent
so you could use

SELECT
T.Charge.value('(../../TrackingNumber)[1]', 'nvarchar(20)') AS
TrackingNumber,
T.Charge.value('(ChargeInformation/NetAmount)[1]', 'float') AS NetAmount
FROM @doc.nodes('/Package/ChargeDetails/Charge') AS T(Charge);

[Will]

Martin,

Simple, ingenious. Thank you ...

Boy, was I headed down the wrong path on that one! In all the web searching
I did, I did not find one example like the simple one you give here. And it
works perfectly.

Am now taking timeout for some XPATH tutorials ....

Will

[Will]

Hi again Martin,

I edited the UPS .xml segment to comprise varying numbers of <NetAmount>
tags between any two charge tags, as I have to assume that this is a
possibility within the actual UPS .xml doc.

Now, since .value() method can only return singletons, how would I return
the TrackingNumber and NetAmount as relational data? Is this where FLWOR
comes into play?

Assuming the following .xml segment I would need the code to return:

TrackingNumber NetAmount
1Z11100503506111 1.04
1Z11100503506111 2.04
1Z11100503506111 3.04
1Z11100503506111 1.05
1Z11100503506111 2.05
1Z11100503506111 3.05
1Z11100503506111 4.05
1Z11100503506111 1.23
1Z11100503506111 2.23
1Z11100503506111 3.23
1Z11100503506111 4.23
1Z11100503506111 5.23

<Package>
<TrackingNumber>1Z11100503506111</TrackingNumber>
<Zone>004</Zone>
<ContainerType>PKG</ContainerType>
<PackageReferences>
<Reference>
<Sequence>1</Sequence>
<ReferenceNumber>JOBBAGS</ReferenceNumber>
</Reference>
<Reference>
<Sequence>2</Sequence>
<ReferenceNumber>FF</ReferenceNumber>
</Reference>
</PackageReferences>
<PackageQuantity>
<ActualQuantity>
<Quantity>1</Quantity>
</ActualQuantity>
</PackageQuantity>
<PackageWeight>
<ActualWeight>
<Weight>11.0</Weight>
<UnitOfMeasure>L</UnitOfMeasure>
</ActualWeight>
<BilledWeight>
<Weight>11.0</Weight>
<UnitOfMeasure>L</UnitOfMeasure>
</BilledWeight>
</PackageWeight>
<ChargeDetails>

<Charge>
<ClassificationCode>FRT</ClassificationCode>
<ChargeInformation>

<DescriptionCode>003 </DescriptionCode>
<DescriptionOfCharges>Ground Residential</DescriptionOfCharges>
<CurrencyCode>USD</CurrencyCode>
<IncentiveAmount>1.96</IncentiveAmount>
<NetAmount>1.04</NetAmount>
<NetAmount>2.04</NetAmount>
<NetAmount>3.04</NetAmount>
</ChargeInformation>
</Charge>
<Charge>
<ClassificationCode>ACC</ClassificationCode>
<ChargeInformation>
<DescriptionCode>RES </DescriptionCode>
<DescriptionOfCharges>Residential Surcharge</DescriptionOfCharges>
<ChargedUnitQuantity>0000001</ChargedUnitQuantity>
<CurrencyCode>USD</CurrencyCode>
<IncentiveAmount>0.00</IncentiveAmount>
<NetAmount>1.05</NetAmount>
<NetAmount>2.05</NetAmount>
<NetAmount>3.05</NetAmount>

<NetAmount>4.05</NetAmount>
</ChargeInformation>
</Charge>
<Charge>

<ClassificationCode>FSC</ClassificationCode>
<ChargeInformation>
<DescriptionCode>FSC </DescriptionCode>
<DescriptionOfCharges>Fuel Surcharge</DescriptionOfCharges>
<ChargedUnitQuantity>0000001</ChargedUnitQuantity>
<BasisCurrencyCode>USD</BasisCurrencyCode>
<BasisValue>9.05</BasisValue>
<CurrencyCode>USD</CurrencyCode>
<IncentiveAmount>0.06</IncentiveAmount>
<NetAmount>1.23</NetAmount>
<NetAmount>2.23</NetAmount>
<NetAmount>3.23</NetAmount>
<NetAmount>4.23</NetAmount>
<NetAmount>5.23</NetAmount>
</ChargeInformation>
</Charge>
</ChargeDetails>
</Package>

[Martin Honnen]

Will wrote:

> Now, since .value() method can only return singletons, how would I return
> the TrackingNumber and NetAmount as relational data? Is this where FLWOR
> comes into play?

No, the argument to the nodes method determines which elements in your
XML are mapped to rows. You seem to want to map each NetAmount element
to a row so use e.g.

SELECT
T.NetAmount.value('(../../../../TrackingNumber)[1]', 'nvarchar(20)')
AS TrackingNumber,
T.NetAmount.value('.', 'float') AS NetAmount
FROM
@xml.nodes('/Package/ChargeDetails/Charge/ChargeInformation/NetAmount')
AS T(NetAmount);

[Will]

That worked. Yes ... that is exactly what I needed to do.

Very interesting that I can get TrackingNumber from that node method
expression since the rows returned from the node expression comprise only the
NetAmount values. Did not realize I could do that, but that adds tremendous
power.

Your answers have been extremely helpful - would have taken me ages to get
to this level of understanding without them. As always ... thank you!

Will