Help on XML Explicit

Hello, I am starting to work with XML Explicit. I am having problems
with the tags and the level they generate in.
Can anyone please help me, for I have looked aroung and it seems that I
am doing everything fine!!!
I am attaching below an example of the query and the XML it generates.
===========================
Declare @.IdCat int
set @.IdCat = 62
SELECT
1 AS TAG,
NULL AS PARENT,
'' AS [p!1],
NULL AS [pc!2],
NULL AS [pc!2!npc!xml],
NULL AS [pc!2!idp!xml],
NULL AS [pc!2!nc!xml],
NULL AS [pc!2!m!xml],
NULL AS [e!3!xml],
NULL AS [e!3!ce!xml],
NULL AS [e!3!de!xml],
NULL AS [e!3!fe!xml]
FROM tDE_Cataporte DE_Cat
WHERE DE_Cat.IdCat = @.IdCat
UNION ALL
SELECT
2 AS TAG,
1 AS PARENT,
NULL AS [p!1],
'' AS [pc!2],
DE_Pl.NumPlaCli AS [pc!2!npc!xml],
DE_Pl.IdPla AS [pc!2!idp!xml],
DE_Pl.NumCta AS [pc!2!nc!xml],
DE_Pl.Monto AS [pc!2!m!xml],
NULL AS [e!3!xml],
NULL AS [e!3!ce!xml],
NULL AS [e!3!de!xml],
NULL AS [e!3!fe!xml]
FROM tDE_Planilla DE_Pl
WHERE DE_Pl.IdCat = @.IdCat
UNION ALL
SELECT
3 AS TAG,
2 AS PARENT,
NULL AS [p!1],
NULL AS [pc!2],
NULL AS [pc!2!npc!xml],
NULL AS [pc!2!idp!xml],
NULL AS [pc!2!nc!xml],
NULL AS [pc!2!m!xml],
'' AS [e!3!xml],
DE_Est.CodEst AS [e!3!ce!xml],
DE_Est.DesEst AS [e!3!de!xml],
DE_PlEstObs.FecEst AS [e!3!fe!xml]
FROM tDE_PlanillaxEstado_Observacion DE_PlEstObs INNER JOIN
tDE_Planilla DE_Pl ON DE_PlEstObs.IdPla =
DE_Pl.IdPla INNER JOIN
tDE_Estado DE_Est ON DE_PlEstObs.CodEst =
DE_Est.CodEst
WHERE DE_Pl.IdCat = @.IdCat
AND DE_PlEstObs.FecEst = (SELECT MIN(DE_PlEstObs2.FecEst) FROM
tDE_PlanillaxEstado_Observacion DE_PlEstObs2 WHERE
DE_PlEstObs2.IdPla=DE_Pl.IdPla)
FOR XML Explicit
<p>
<pc>
<npc>888</npc>
<idp>58</idp>
<nc>9939</nc>
<m>20000</m>
</pc>
<pc>
<npc>555</npc>
<idp>60</idp>
<nc>00018</nc>
<m>131150</m>
</pc>
<pc>
<npc>753</npc>
<idp>61</idp>
<nc>20018</nc>
<m>40300</m>
<e xml="">
<ce>0</ce>
<de>Borrador</de>
<fe>2005-07-07T16:06:04.130</fe>
</e>
<e xml="">
<ce>0</ce>
<de>Borrador</de>
<fe>2005-07-08T10:40:12.390</fe>
</e>
<e xml="">
<ce>0</ce>
<de>Borrador</de>
<fe>2005-07-08T11:39:32.830</fe>
</e>
</pc>
</p>
========================================
==================
As you can see the tag 3 (<e xml=""> is only generated at the end when
there should be one <e> for each <pc>.
Thank you in advance for your help.Please post DDL and some sample data. Can't guess your data.
ML

HELP ON XML

Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHORHello,
Try this (obvious) query:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT 'Customer CustomerID="'+CustomerID
+'" ContactName="'+ContactName+'"' AS COLONE
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
Is this what you need ?
Razvan|||Hi Razvan,
Thanxs But this will not work. what I actually want is to get all inner
attribute of a xml. here you are concating ContactName...but I dont want to
have a hardcoding like this. client can pass Name , Cname...any thing. I jus
t
want a list of all all attribute.
I have tried this also
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT CustomerID ,ContactName
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)
)
for xml auto
But gave me error
Unnamed column or table names cannot be used as XML identifiers. Name
unnamed columns using AS in the SELECT statement.
Regards,
Kishor
"Razvan Socol" wrote:

> Hello,
> Try this (obvious) query:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
>
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT 'Customer CustomerID="'+CustomerID
> +'" ContactName="'+ContactName+'"' AS COLONE
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> Is this what you need ?
> Razvan
>|||> here you are concating ContactName...
> but I dont want to have a hardcoding like this.
You already did hardcoding: in the parameters of the OPENXML function,
in the WITH clause.

> I have tried this also [...] for xml auto [...] But gave me error
[...]
Try this:
[...]
SELECT * INTO #tmp
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
SELECT * FROM #tmp FOR XML AUTO
DROP TABLE #tmp
Razvan|||Yes,
Just to explain you all I have done .. I just want inner attributes...
if you know .. let me know.
TIA
Kishor
"Razvan Socol" wrote:

> You already did hardcoding: in the parameters of the OPENXML function,
> in the WITH clause.
>
> [...]
> Try this:
> [...]
> SELECT * INTO #tmp
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> SELECT * FROM #tmp FOR XML AUTO
> DROP TABLE #tmp
> Razvan
>

HELP ON XML

Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHORIf what you want is the <Customer> element with all attributes, then you can
use this code:
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (Customer varchar(100) '@.mp:xmltext')
This will return the following 2 rows:
<Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
On the other hand, if you want the literal strings you specified in your
post, you could do it by just concatenating the values from the resultset
like this:
SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
ContactName + '"'
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (nodeName varchar(10) '@.mp:localname',
CustomerID varchar(10),
ContactName varchar(20))
This gives you these 2 rows:
Customer CustomerID ="VINET" ContactName=Paul Henriot"
Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
(you could just specify a literal "Customer" instead of retrieving the node
name like I've done.)
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
'Customer CustomerID="VINET" ContactName="Paul Henriot"'
'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
Please suggest me some ways to achieve this
TIA,
KISHOR|||Hi Graeme Malcolm,
Thanxs for your solution, This worked ...
'@.mp:xmltext'
Regards,
Kishor.
"Graeme Malcolm" wrote:

> If what you want is the <Customer> element with all attributes, then you c
an
> use this code:
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',2)
> WITH (Customer varchar(100) '@.mp:xmltext')
> This will return the following 2 rows:
> <Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
> On the other hand, if you want the literal strings you specified in your
> post, you could do it by just concatenating the values from the resultset
> like this:
> SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
> ContactName + '"'
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (nodeName varchar(10) '@.mp:localname',
> CustomerID varchar(10),
> ContactName varchar(20))
> This gives you these 2 rows:
> Customer CustomerID ="VINET" ContactName=Paul Henriot"
> Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
> (you could just specify a literal "Customer" instead of retrieving the nod
e
> name like I've done.)
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "kishor" <kishor@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> Hi all,
> I have a small query, may be this is not supported in SQL 2000. But at lea
st
> I want some round about way, which will solve my problem. I am here pastin
g
> working code.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> This Query will give me result
> CustomerID ContactName
> -- --
> VINET Paul Henriot
> LILAS Carlos Gonzlez
> This is fine but I want to get results like this.
> COLONE
> 'Customer CustomerID="VINET" ContactName="Paul Henriot"'
> 'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
> Please suggest me some ways to achieve this
> TIA,
> KISHOR
>
>
>

HELP ON XML

Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHOR
If what you want is the <Customer> element with all attributes, then you can
use this code:
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (Customer varchar(100) '@.mp:xmltext')
This will return the following 2 rows:
<Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
On the other hand, if you want the literal strings you specified in your
post, you could do it by just concatenating the values from the resultset
like this:
SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
ContactName + '"'
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (nodeName varchar(10) '@.mp:localname',
CustomerID varchar(10),
ContactName varchar(20))
This gives you these 2 rows:
Customer CustomerID ="VINET" ContactName=Paul Henriot"
Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
(you could just specify a literal "Customer" instead of retrieving the node
name like I've done.)
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
'Customer CustomerID="VINET" ContactName="Paul Henriot"'
'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
Please suggest me some ways to achieve this
TIA,
KISHOR
|||Hi Graeme Malcolm,
Thanxs for your solution, This worked ...
'@.mp:xmltext'
Regards,
Kishor.
"Graeme Malcolm" wrote:

> If what you want is the <Customer> element with all attributes, then you can
> use this code:
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',2)
> WITH (Customer varchar(100) '@.mp:xmltext')
> This will return the following 2 rows:
> <Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
> On the other hand, if you want the literal strings you specified in your
> post, you could do it by just concatenating the values from the resultset
> like this:
> SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
> ContactName + '"'
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (nodeName varchar(10) '@.mp:localname',
> CustomerID varchar(10),
> ContactName varchar(20))
> This gives you these 2 rows:
> Customer CustomerID ="VINET" ContactName=Paul Henriot"
> Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
> (you could just specify a literal "Customer" instead of retrieving the node
> name like I've done.)
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "kishor" <kishor@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> Hi all,
> I have a small query, may be this is not supported in SQL 2000. But at least
> I want some round about way, which will solve my problem. I am here pasting
> working code.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> This Query will give me result
> CustomerID ContactName
> -- --
> VINET Paul Henriot
> LILAS Carlos Gonzlez
> This is fine but I want to get results like this.
> COLONE
> 'Customer CustomerID="VINET" ContactName="Paul Henriot"'
> 'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
> Please suggest me some ways to achieve this
> TIA,
> KISHOR
>
>
>

Help newbie with quering XML

Hello,
I have XML data which I receive from a vendor and store in a table
with the XML datatype in this arrangement:
<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>
How do I query for item1 and return just, widget ?
TIA,
RichHi,
How about something like this
DECLARE @.x XML
SET @.x =
'<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>'
SELECT @.x.value('(//ITEM[@.name="item1"]/@.value)[1]','nvarchar(MAX)')
If you want to parameterize the value for the name attribute, you can
use sql:variable() and do this
DECLARE @.x XML
SET @.x =
'<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>'
DECLARE @.n nvarchar(MAX)
SET @.n = 'item1'
SELECT
@.x.value('(//ITEM[@.name=sql:variable("@.n")]/@.value)[1]','nvarchar(MAX)')
I hope this helps
Denis Ruckebusch
XML datatype test team
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sk8man31" <me@.aol.com> wrote in message
news:rc71825rqgo28vr6dr18beb0bn1jv148qe@.
4ax.com...
> Hello,
> I have XML data which I receive from a vendor and store in a table
> with the XML datatype in this arrangement:
> <MYXML>
> <ITEMS>
> <ITEM name="item1" value="widget" />
> <ITEM name="item2" value="dongle" />
> <ITEM name="item3" value="thingy" />
> </ITEMS>
> </MYXML>
> How do I query for item1 and return just, widget ?
> TIA,
> Rich|||On Fri, 2 Jun 2006 18:18:53 -0700, "Denis Ruckebusch [MSFT]"
<denisruc@.online.microsoft.com> wrote:

>Hi,
> How about something like this
>DECLARE @.x XML
>SET @.x =
>'<MYXML>
><ITEMS>
><ITEM name="item1" value="widget" />
><ITEM name="item2" value="dongle" />
><ITEM name="item3" value="thingy" />
></ITEMS>
></MYXML>'
>SELECT @.x.value('(//ITEM[@.name="item1"]/@.value)[1]','nvarchar(MAX)')
>
>If you want to parameterize the value for the name attribute, you can
>use sql:variable() and do this
>DECLARE @.x XML
>SET @.x =
>'<MYXML>
><ITEMS>
><ITEM name="item1" value="widget" />
><ITEM name="item2" value="dongle" />
><ITEM name="item3" value="thingy" />
></ITEMS>
></MYXML>'
>DECLARE @.n nvarchar(MAX)
>SET @.n = 'item1'
>SELECT
>@.x.value('(//ITEM[@.name=sql:variable("@.n")]/@.value)[1]','nvarchar(MAX)')
>
>I hope this helps
>Denis Ruckebusch
>XML datatype test team
Denis,
Thanks for your help, that worked great!
~Rich

Help needed with Xquery

Hello,

I'm trying to retreive the values from multiple nodes based on the value of another , without any success. The XML source is stored in an SQL(2005) xml column .

'Sample XML

<!--Combat Flight Sim mission-->

<Mission>

<Params Version="3.0" Directive="nothing" Country="Britain" Aircraft="p_51b" Airbase="brod23" Date="8/10/1940" Time="12:00" Weather="scatteredclouds3.xml" Multiplayer="y" MultiplayerOnly="n" />

.......

<AirFormation ID="6003" Directive="nothing" Country="Britain" Skill="1" FormType="diamond">

<Unit ID="9459" Type="p_51b" IsPlayer="y" Skill="1" />

<Unit ID="9460" Type="p_51b" Skill="2" />

.........

<AirFormation ID="6000" Directive="nothing" Country="Britain" Points="2" DamagePercent="40" Skill="2" Payload="2" FormType="box">

<Unit ID="9467" Type="b_25c" Skill="2" Payload="3" />

<Unit ID="9468" Type="b_25c" Skill="2" Payload="3" />

.........

AirFormation ID="6007" Directive="nothing" Country="Germany" Skill="2" FormType="fingertip">

<Unit ID="9475" Type="bf_109g_6" Skill="2" Payload="6" />

<Unit ID="9476" Type="bf_109g_6" Skill="2"

'This is the SQL code:

SELECT DISTINCT nref.value('@.Type', 'varchar(100)') Aircraft

FROM dbo.MOG_Missions CROSS APPLY xmlData.nodes('//AirFormation/Unit') as T(nref)

WHERE id = @.id 'some additional condition here is needed but I cannot figure it out

Which returns the following values from the ?Type attribute :

b_25c
bf_109g_6
p_51b

What I would like to accomplish is to return only the values from ?Type where the AirFormation-Country attribute matches the ?Country attribute of the ?Params node.

Thank you in advance.

Your XML sample is not clear to me. What is the relationship between the Params element and the AirFormation elements? If that is known then you should simply be able to express the condition in an XPath predicate in your nodes call. For example if the Params element is a sibling of the AirFormation elements then you can check e.g.

Code Snippet

SELECT DISTINCT t.u.value('@.Type', 'nvarchar(10)') AS Type

FROM example1

CROSS APPLY xml.nodes('//AirFormation[@.Country = ../Params/@.Country]/Unit') AS t(u)

WHERE id = 3;

|||I should have asked for help sooner! Thank you so much!

Help needed with KERBROS and Native XML Web Services

Trying to get Native XML Web Services setup in our test enviroment, and I've
hit a problem.
When the HTTP EndPoint is set to use Integrated Authentication, I can browse
to the endpoint (using IE7 from a seperate PC) and get the WSDL back, but
when I switch the EndPoint to use KERBEROS authentication, I get nothing
returned, and only see a blank page.
All machines are in the same Active Directory domain. Using SQL Server 2005
SP2 on Win2003 Std SP1 on the server, and XP SP2 and IE7 on the PC.
The SQL Server is running under a local domain account, and this account has
been registered for both the MSSQLSvc and HTTP services, as below (the names
have been changed to protect the guilty).
MSSQLSvc/Server1.test.local:1433
MSSQLSvc/Server1:1433
HTTP/Server1.test.local
HTTP/Server1
The EndPoint name has been reserved using sp_reserve_http_namespace, and is
owned by SA. I'll be changing the auditting to log all authentication event.
So, anyone has any ideas or guidance'
Thanks in advance,
AlHi Al
Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
John
"Al" wrote:
> Trying to get Native XML Web Services setup in our test enviroment, and I've
> hit a problem.
> When the HTTP EndPoint is set to use Integrated Authentication, I can browse
> to the endpoint (using IE7 from a seperate PC) and get the WSDL back, but
> when I switch the EndPoint to use KERBEROS authentication, I get nothing
> returned, and only see a blank page.
> All machines are in the same Active Directory domain. Using SQL Server 2005
> SP2 on Win2003 Std SP1 on the server, and XP SP2 and IE7 on the PC.
> The SQL Server is running under a local domain account, and this account has
> been registered for both the MSSQLSvc and HTTP services, as below (the names
> have been changed to protect the guilty).
> MSSQLSvc/Server1.test.local:1433
> MSSQLSvc/Server1:1433
> HTTP/Server1.test.local
> HTTP/Server1
> The EndPoint name has been reserved using sp_reserve_http_namespace, and is
> owned by SA. I'll be changing the auditting to log all authentication event.
> So, anyone has any ideas or guidance'
> Thanks in advance,
> Al|||When I do the
ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
I don't get the WSDL. But I have discovered that the endpoint still accepts
and processes calls to the web services on the EndPoint.
When I do
ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
the WSDL.
Could it be that using KERBEROS authentication disables the WSDL discovery?
"John Bell" wrote:
> Hi Al
> Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> John
>|||Hi
I am not sure if this is the case and can't find any documentation to say
so. Have you tried AUTHENTICATION=KERBEROS,NTLM and AUTHENTICATION=NTLM,
KERBEROS to see if there are any differences?
John
"Al" wrote:
> When I do the
> ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
> I don't get the WSDL. But I have discovered that the endpoint still accepts
> and processes calls to the web services on the EndPoint.
> When I do
> ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
> the WSDL.
> Could it be that using KERBEROS authentication disables the WSDL discovery?
> "John Bell" wrote:
> > Hi Al
> >
> > Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> > SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> >
> > John
> >|||Hi John,
I changed the WS to return some details from sys.dm_exec_connections as
well, so I could see a little more of what was going on when calling the WS.
When I have specified NTLM as an Authentication method (position doesn't
appear to make a difference), then I can get a WSDL back (with IE7).
If I have both NTLM and KERBEROS, or INTEGRATED by itself, then the
connection is made as NEGOTIATE.
NTLM by itself gets the WSDL back, and is made as NTLM
KERBEROS by itself doesn't return a WSDL and is made as KERBEROS.
But what I have now seen (because I did a refresh instead of using a new IE
tab), if IE7 has displayed the WSDL, and then I switch the EndPoint to
KERBEROS only, then it displays the following instead of the blank page I've
usually had.
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then
click the Refresh button, or try again later.
----
Access is denied. Error processing resource 'http://apollo/SQLTestEP?wsdl'.
I've checked, and IE7 thinks the web site is in the "Local Intranet", so I'm
assuming that the Windows credentials are passed straight through. And it
seems odd that I can call the WS from C#, but get an "Access is denied" from
IE7.
"John Bell" wrote:
> Hi
> I am not sure if this is the case and can't find any documentation to say
> so. Have you tried AUTHENTICATION=KERBEROS,NTLM and AUTHENTICATION=NTLM,
> KERBEROS to see if there are any differences?
> John
> "Al" wrote:
> > When I do the
> > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
> > I don't get the WSDL. But I have discovered that the endpoint still accepts
> > and processes calls to the web services on the EndPoint.
> >
> > When I do
> > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
> > the WSDL.
> >
> > Could it be that using KERBEROS authentication disables the WSDL discovery?
> >
> > "John Bell" wrote:
> >
> > > Hi Al
> > >
> > > Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> > > SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> > >
> > > John
> > >|||Hi
Does this mean you are using custom WDSL, does default change the behavior?
John
"Al" wrote:
> Hi John,
> I changed the WS to return some details from sys.dm_exec_connections as
> well, so I could see a little more of what was going on when calling the WS.
> When I have specified NTLM as an Authentication method (position doesn't
> appear to make a difference), then I can get a WSDL back (with IE7).
> If I have both NTLM and KERBEROS, or INTEGRATED by itself, then the
> connection is made as NEGOTIATE.
> NTLM by itself gets the WSDL back, and is made as NTLM
> KERBEROS by itself doesn't return a WSDL and is made as KERBEROS.
> But what I have now seen (because I did a refresh instead of using a new IE
> tab), if IE7 has displayed the WSDL, and then I switch the EndPoint to
> KERBEROS only, then it displays the following instead of the blank page I've
> usually had.
> The XML page cannot be displayed
> Cannot view XML input using style sheet. Please correct the error and then
> click the Refresh button, or try again later.
> ----
> Access is denied. Error processing resource 'http://apollo/SQLTestEP?wsdl'.
> I've checked, and IE7 thinks the web site is in the "Local Intranet", so I'm
> assuming that the Windows credentials are passed straight through. And it
> seems odd that I can call the WS from C#, but get an "Access is denied" from
> IE7.
> "John Bell" wrote:
> > Hi
> >
> > I am not sure if this is the case and can't find any documentation to say
> > so. Have you tried AUTHENTICATION=KERBEROS,NTLM and AUTHENTICATION=NTLM,
> > KERBEROS to see if there are any differences?
> >
> > John
> >
> > "Al" wrote:
> >
> > > When I do the
> > > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
> > > I don't get the WSDL. But I have discovered that the endpoint still accepts
> > > and processes calls to the web services on the EndPoint.
> > >
> > > When I do
> > > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
> > > the WSDL.
> > >
> > > Could it be that using KERBEROS authentication disables the WSDL discovery?
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Al
> > > >
> > > > Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> > > > SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> > > >
> > > > John
> > > >|||Hi
The HTTP EndPoint has been created with WSDL = STANDARD (i.e.
WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]').
"John Bell" wrote:
> Hi
> Does this mean you are using custom WDSL, does default change the behavior?
> John
> "Al" wrote:
> > Hi John,
> >
> > I changed the WS to return some details from sys.dm_exec_connections as
> > well, so I could see a little more of what was going on when calling the WS.
> >
> > When I have specified NTLM as an Authentication method (position doesn't
> > appear to make a difference), then I can get a WSDL back (with IE7).
> > If I have both NTLM and KERBEROS, or INTEGRATED by itself, then the
> > connection is made as NEGOTIATE.
> > NTLM by itself gets the WSDL back, and is made as NTLM
> > KERBEROS by itself doesn't return a WSDL and is made as KERBEROS.
> >
> > But what I have now seen (because I did a refresh instead of using a new IE
> > tab), if IE7 has displayed the WSDL, and then I switch the EndPoint to
> > KERBEROS only, then it displays the following instead of the blank page I've
> > usually had.
> >
> > The XML page cannot be displayed
> > Cannot view XML input using style sheet. Please correct the error and then
> > click the Refresh button, or try again later.
> > ----
> > Access is denied. Error processing resource 'http://apollo/SQLTestEP?wsdl'.
> >
> > I've checked, and IE7 thinks the web site is in the "Local Intranet", so I'm
> > assuming that the Windows credentials are passed straight through. And it
> > seems odd that I can call the WS from C#, but get an "Access is denied" from
> > IE7.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I am not sure if this is the case and can't find any documentation to say
> > > so. Have you tried AUTHENTICATION=KERBEROS,NTLM and AUTHENTICATION=NTLM,
> > > KERBEROS to see if there are any differences?
> > >
> > > John
> > >
> > > "Al" wrote:
> > >
> > > > When I do the
> > > > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
> > > > I don't get the WSDL. But I have discovered that the endpoint still accepts
> > > > and processes calls to the web services on the EndPoint.
> > > >
> > > > When I do
> > > > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
> > > > the WSDL.
> > > >
> > > > Could it be that using KERBEROS authentication disables the WSDL discovery?
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Al
> > > > >
> > > > > Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> > > > > SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> > > > >
> > > > > John
> > > > >

Help needed with importing XML

SQL server 2005
ive been trying very unsucessfully to try and import an xml file into a
SQL2005 table. I have a schema but am unsure as what to do next. I have bee
n
looking at lots od different exmples but its way over my head. i need a ste
p
by step example that will import the following xml file into two tables
<?xml version="1.0" encoding="ISO-8859-1"?>
<BACSDocument>
<Data>
<ARUCS>
<Header reportType="REFT1027" adviceNumber="01077"
currentProcessingDate="2005-12-05"></Header>
<AddresseeInformation name="Mr Bean "
address1="Company Name " address2="This Place "
address3="This Town " address4="This County
" address5="A12 45T "></AddresseeInformation>
</ARUCS>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>
table 1 will contain all the header information in fields , reportType,
advicenumber etc as all the address info into table 2.
Once i have created the tables, is there a way to just do the import direct
in TSQL
any help will be very welcome as im struggling to grasp thisHi Peter,
I've made up an really simple table definition for table1 and table2 and
left some of the columns out to cut down on size. You want to decompose the
XML into relational name-value pairs and do an insert of pieces into two
separate tables. This is *assuming* you don't have more than one Header or
AddresseeInformation in the document, or more than one document in the XML.
Example follows mail message.
First way is the easiest. Use the xml.value method to extract each value,
given the attribute name in the document, 1 column per column in the rowset
(table) you want. This may be able to be optimized by changing the query,
but I'm trying to keep it simple for exposition.
Second way is to use xml.nodes method to obtain a rowset of name-value
pairs. Then use the PIVOT operator to pivot the values into one row and
multiple column values. I've done it in two steps (#temp table) to
illustrate what nodes returns, then combined it into one step.
You could also use OpenXML to do this, but it might require more storage
overhead.
Hope this helps,
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
CREATE TABLE table1 (
reporttype varchar(100),
adviceNumber varchar(100),
currentProcessingDate datetime
)
go
CREATE TABLE table2 (
name varchar(100),
address1 varchar(100),
address2 varchar(100)
)
go
declare @.x xml
set @.x =
'<?xml version="1.0" encoding="ISO-8859-1"?>
<BACSDocument>
<Data>
<ARUCS>
<Header reportType="REFT1027" adviceNumber="01077"
currentProcessingDate="2005-12-05"></Header>
<AddresseeInformation name="Mr Bean "
address1="Company Name " address2="This Place "
address3="This Town " address4="This County
" address5="A12 45T "></AddresseeInformation>
</ARUCS>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>
'
/* First way, using xml.value
INSERT table1
select @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.reportType',
'varchar(100)') as a,
@.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.adviceNumber',
'varchar(100)') as b,
@.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.currentProcessingDate',
'datetime') as c
SELECT * FROM table1
-- now do the same for table2
INSERT table2
select @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.name',
'varchar(100)') as a,
@.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address1',
'varchar(100)') as b,
@.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address2',
'varchar(100)') as c
SELECT * FROM table2
*/
/* Second way, using xml.nodes, intermediate table for exposition
select t.c.value('local-name(.)', 'varchar(50)') as Name,
t.c.value('data(.)', 'varchar(100)') as Value
into #temp
from @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
select * from #temp
SELECT [reportType], [adviceNumber], [currentProcessingDate]
FROM #temp
PIVOT (
MAX([Value]) FOR
[Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
) as p
-- now do the same for table2 (elided)
*/
-- third way, combination of second way into one statement.
insert table1
select [reportType], [adviceNumber], [currentProcessingDate]
from
(
SELECT t.c.value('local-name(.)', 'varchar(50)') as [Name],
t.c.value('data(.)', 'varchar(100)') as [Value]
FROM @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
) AS namevalue
PIVOT (
MAX([Value]) FOR
[Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
) as p
-- now do the same for table2 (elided)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:E8722036-4DD5-4F15-A4FB-6CF9BF9044EF@.microsoft.com...
> SQL server 2005
> ive been trying very unsucessfully to try and import an xml file into a
> SQL2005 table. I have a schema but am unsure as what to do next. I have
> been
> looking at lots od different exmples but its way over my head. i need a
> step
> by step example that will import the following xml file into two tables
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument>
> <Data>
> <ARUCS>
> <Header reportType="REFT1027" adviceNumber="01077"
> currentProcessingDate="2005-12-05"></Header>
> <AddresseeInformation name="Mr Bean "
> address1="Company Name " address2="This Place
> "
> address3="This Town " address4="This County
> " address5="A12 45T "></AddresseeInformation>
> </ARUCS>
> </Data>
> <SignatureMethod></SignatureMethod>
> <Signature></Signature>
> </BACSDocument>
> table 1 will contain all the header information in fields , reportType,
> advicenumber etc as all the address info into table 2.
> Once i have created the tables, is there a way to just do the import
> direct
> in TSQL
> any help will be very welcome as im struggling to grasp this|||Bob,
Thank you very much, that has showed me a lot and i have managed to adapt it
to the nomal xml files that im currently recieving, however, and theres
always a however, some of the files have more than one element of the ssame
name, so in the file i have shown earlier, how would i handle it if say it
had three AddresseeInformation for example
thansk in advance
"Bob Beauchemin" wrote:

> Hi Peter,
> I've made up an really simple table definition for table1 and table2 and
> left some of the columns out to cut down on size. You want to decompose th
e
> XML into relational name-value pairs and do an insert of pieces into two
> separate tables. This is *assuming* you don't have more than one Header or
> AddresseeInformation in the document, or more than one document in the XML
.
> Example follows mail message.
> First way is the easiest. Use the xml.value method to extract each value,
> given the attribute name in the document, 1 column per column in the rowse
t
> (table) you want. This may be able to be optimized by changing the query,
> but I'm trying to keep it simple for exposition.
> Second way is to use xml.nodes method to obtain a rowset of name-value
> pairs. Then use the PIVOT operator to pivot the values into one row and
> multiple column values. I've done it in two steps (#temp table) to
> illustrate what nodes returns, then combined it into one step.
> You could also use OpenXML to do this, but it might require more storage
> overhead.
> Hope this helps,
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
> CREATE TABLE table1 (
> reporttype varchar(100),
> adviceNumber varchar(100),
> currentProcessingDate datetime
> )
> go
> CREATE TABLE table2 (
> name varchar(100),
> address1 varchar(100),
> address2 varchar(100)
> )
> go
> declare @.x xml
> set @.x =
> '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument>
> <Data>
> <ARUCS>
> <Header reportType="REFT1027" adviceNumber="01077"
> currentProcessingDate="2005-12-05"></Header>
> <AddresseeInformation name="Mr Bean "
> address1="Company Name " address2="This Place
"
> address3="This Town " address4="This County
> " address5="A12 45T "></AddresseeInformation>
> </ARUCS>
> </Data>
> <SignatureMethod></SignatureMethod>
> <Signature></Signature>
> </BACSDocument>
> '
> /* First way, using xml.value
> INSERT table1
> select @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.reportType',
> 'varchar(100)') as a,
> @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.adviceNumber',
> 'varchar(100)') as b,
> @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.currentProcessingDa
te',
> 'datetime') as c
> SELECT * FROM table1
> -- now do the same for table2
> INSERT table2
> select @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.name'
,
> 'varchar(100)') as a,
> @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.addre
ss1',
> 'varchar(100)') as b,
> @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.addre
ss2',
> 'varchar(100)') as c
> SELECT * FROM table2
> */
>
> /* Second way, using xml.nodes, intermediate table for exposition
> select t.c.value('local-name(.)', 'varchar(50)') as Name,
> t.c.value('data(.)', 'varchar(100)') as Value
> into #temp
> from @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
> select * from #temp
> SELECT [reportType], [adviceNumber], [currentProcessingDate]
> FROM #temp
> PIVOT (
> MAX([Value]) FOR
> [Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
> ) as p
> -- now do the same for table2 (elided)
> */
> -- third way, combination of second way into one statement.
> insert table1
> select [reportType], [adviceNumber], [currentProcessingDate]
> from
> (
> SELECT t.c.value('local-name(.)', 'varchar(50)') as [Name],
> t.c.value('data(.)', 'varchar(100)') as [Value]
> FROM @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
> ) AS namevalue
> PIVOT (
> MAX([Value]) FOR
> [Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
> ) as p
> -- now do the same for table2 (elided)
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:E8722036-4DD5-4F15-A4FB-6CF9BF9044EF@.microsoft.com...
>
>|||Hi Peter,
Those examples showed how to decompose arbitrary XML in multiple unrealated
tables. In a relational database you need to have something tying together
table1 and table2 (adviseNumber?, reportType?). There's nothing in the
document to deduce this. Also, relational doesn't allow repeating groups, so
you'd need a discriminator to distinguish between the 3 nodes. You could
either use ordinal (as I used [1] in the first example to indicate the 1st
AddresseeInformation) or use nodes to do it in one step and insert multiple
rows. But there has to be something in the relational schema tying table1
and table2 together.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3D443871-A973-4407-96EE-5E44EA309BBF@.microsoft.com...
> Bob,
> Thank you very much, that has showed me a lot and i have managed to adapt
> it
> to the nomal xml files that im currently recieving, however, and theres
> always a however, some of the files have more than one element of the
> ssame
> name, so in the file i have shown earlier, how would i handle it if say
> it
> had three AddresseeInformation for example
> thansk in advance
> "Bob Beauchemin" wrote:
>|||Bob,
Ive spent he entire day chasing ghosts tying to assing a discriminator , its
easy to code when i know how meny there will be but as these reports are
dynamic i need to find a way to change rowcount to equal the ordinal, and
loop till all the rows have been imported
SELECT t.c.value('local-name(.)', 'varchar(50)') AS Name,
t.c.value('data(.)', 'Varchar(100)') as Value, '1' as RowNumber
INTO #ReturnedItem
FROM
@.XMLDOC.nodes('/BACSDocument/Data/ARUCS/Advice/OriginatingAccountRecords/Ori
ginatingAccountRecord/ReturnedCreditItem[1]/@.*') as t(c)
youve been such a great help so far, think one i can resolve this issue i
can carry on on my own
"Bob Beauchemin" wrote:

> Hi Peter,
> Those examples showed how to decompose arbitrary XML in multiple unrealate
d
> tables. In a relational database you need to have something tying together
> table1 and table2 (adviseNumber?, reportType?). There's nothing in the
> document to deduce this. Also, relational doesn't allow repeating groups,
so
> you'd need a discriminator to distinguish between the 3 nodes. You could
> either use ordinal (as I used [1] in the first example to indicate the 1st
> AddresseeInformation) or use nodes to do it in one step and insert multipl
e
> rows. But there has to be something in the relational schema tying table1
> and table2 together.
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:3D443871-A973-4407-96EE-5E44EA309BBF@.microsoft.com...
>
>|||If I think I'm understanding what you're asking, there's a few ways to do
this. You could add a gratuitous identity column to #temp and use
INSERT...SELECT instead of SELECT INTO. You could loop using a T-SQL
variable until the nodes function returns no nodes, using sql:variable in
the XQuery predicate. You could have also changed the XPath expression to a
FLWOR expression and selected the position, but SQL Server XQuery doesn't
support the "at" portion of "for $x at $y in ..." syntax or the position()
function used outside of the predicate.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:F327D2A5-07D4-4F37-82BA-23B4BEA60226@.microsoft.com...
> Bob,
> Ive spent he entire day chasing ghosts tying to assing a discriminator ,
> its
> easy to code when i know how meny there will be but as these reports are
> dynamic i need to find a way to change rowcount to equal the ordinal, and
> loop till all the rows have been imported
> SELECT t.c.value('local-name(.)', 'varchar(50)') AS Name,
> t.c.value('data(.)', 'Varchar(100)') as Value, '1' as RowNumber
> INTO #ReturnedItem
> FROM
> @.XMLDOC.nodes('/BACSDocument/Data/ARUCS/Advice/OriginatingAccountRecords/O
riginatingAccountRecord/ReturnedCreditItem[1]/@.*')
> as t(c)
>
> youve been such a great help so far, think one i can resolve this issue i
> can carry on on my own
> "Bob Beauchemin" wrote:
>|||Bob,
Thanks again for the pointers yhoi have to admit ive spend a few hours and
srtill carnt grasp it. I understand what you were saying about the
relationship between table 1 and table 2, that i think i can work out, what
im still struggling with is returning the pivot table for the three addresss
( only for example ). I still can not get the identity colum in using your
previous eamaples. sorry to be a pain but can you pint me in the direction
of an example based of what you have already explained
thanks again for all your help
"Bob Beauchemin" wrote:

> If I think I'm understanding what you're asking, there's a few ways to do
> this. You could add a gratuitous identity column to #temp and use
> INSERT...SELECT instead of SELECT INTO. You could loop using a T-SQL
> variable until the nodes function returns no nodes, using sql:variable in
> the XQuery predicate. You could have also changed the XPath expression to
a
> FLWOR expression and selected the position, but SQL Server XQuery doesn't
> support the "at" portion of "for $x at $y in ..." syntax or the position(
)
> function used outside of the predicate.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:F327D2A5-07D4-4F37-82BA-23B4BEA60226@.microsoft.com...
>
>

Help needed with importing XML

SQL server 2005
ive been trying very unsucessfully to try and import an xml file into a
SQL2005 table. I have a schema but am unsure as what to do next. I have been
looking at lots od different exmples but its way over my head. i need a step
by step example that will import the following xml file into two tables
<?xml version="1.0" encoding="ISO-8859-1"?>
<BACSDocument>
<Data>
<ARUCS>
<Header reportType="REFT1027" adviceNumber="01077"
currentProcessingDate="2005-12-05"></Header>
<AddresseeInformation name="Mr Bean "
address1="Company Name " address2="This Place "
address3="This Town " address4="This County
" address5="A12 45T "></AddresseeInformation>
</ARUCS>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>
table 1 will contain all the header information in fields , reportType,
advicenumber etc as all the address info into table 2.
Once i have created the tables, is there a way to just do the import direct
in TSQL
any help will be very welcome as im struggling to grasp this
Hi Peter,
I've made up an really simple table definition for table1 and table2 and
left some of the columns out to cut down on size. You want to decompose the
XML into relational name-value pairs and do an insert of pieces into two
separate tables. This is *assuming* you don't have more than one Header or
AddresseeInformation in the document, or more than one document in the XML.
Example follows mail message.
First way is the easiest. Use the xml.value method to extract each value,
given the attribute name in the document, 1 column per column in the rowset
(table) you want. This may be able to be optimized by changing the query,
but I'm trying to keep it simple for exposition.
Second way is to use xml.nodes method to obtain a rowset of name-value
pairs. Then use the PIVOT operator to pivot the values into one row and
multiple column values. I've done it in two steps (#temp table) to
illustrate what nodes returns, then combined it into one step.
You could also use OpenXML to do this, but it might require more storage
overhead.
Hope this helps,
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
CREATE TABLE table1 (
reporttype varchar(100),
adviceNumber varchar(100),
currentProcessingDate datetime
)
go
CREATE TABLE table2 (
name varchar(100),
address1 varchar(100),
address2 varchar(100)
)
go
declare @.x xml
set @.x =
'<?xml version="1.0" encoding="ISO-8859-1"?>
<BACSDocument>
<Data>
<ARUCS>
<Header reportType="REFT1027" adviceNumber="01077"
currentProcessingDate="2005-12-05"></Header>
<AddresseeInformation name="Mr Bean "
address1="Company Name " address2="This Place "
address3="This Town " address4="This County
" address5="A12 45T "></AddresseeInformation>
</ARUCS>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>
'
/* First way, using xml.value
INSERT table1
select @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.reportType',
'varchar(100)') as a,
@.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.adviceNumber',
'varchar(100)') as b,
@.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.currentProcessingDate',
'datetime') as c
SELECT * FROM table1
-- now do the same for table2
INSERT table2
select @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.name',
'varchar(100)') as a,
@.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address1',
'varchar(100)') as b,
@.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address2',
'varchar(100)') as c
SELECT * FROM table2
*/
/* Second way, using xml.nodes, intermediate table for exposition
select t.c.value('local-name(.)', 'varchar(50)') as Name,
t.c.value('data(.)', 'varchar(100)') as Value
into #temp
from @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
select * from #temp
SELECT [reportType], [adviceNumber], [currentProcessingDate]
FROM #temp
PIVOT (
MAX([Value]) FOR
[Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
) as p
-- now do the same for table2 (elided)
*/
-- third way, combination of second way into one statement.
insert table1
select [reportType], [adviceNumber], [currentProcessingDate]
from
(
SELECT t.c.value('local-name(.)', 'varchar(50)') as [Name],
t.c.value('data(.)', 'varchar(100)') as [Value]
FROM @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
) AS namevalue
PIVOT (
MAX([Value]) FOR
[Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
) as p
-- now do the same for table2 (elided)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:E8722036-4DD5-4F15-A4FB-6CF9BF9044EF@.microsoft.com...
> SQL server 2005
> ive been trying very unsucessfully to try and import an xml file into a
> SQL2005 table. I have a schema but am unsure as what to do next. I have
> been
> looking at lots od different exmples but its way over my head. i need a
> step
> by step example that will import the following xml file into two tables
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument>
> <Data>
> <ARUCS>
> <Header reportType="REFT1027" adviceNumber="01077"
> currentProcessingDate="2005-12-05"></Header>
> <AddresseeInformation name="Mr Bean "
> address1="Company Name " address2="This Place
> "
> address3="This Town " address4="This County
> " address5="A12 45T "></AddresseeInformation>
> </ARUCS>
> </Data>
> <SignatureMethod></SignatureMethod>
> <Signature></Signature>
> </BACSDocument>
> table 1 will contain all the header information in fields , reportType,
> advicenumber etc as all the address info into table 2.
> Once i have created the tables, is there a way to just do the import
> direct
> in TSQL
> any help will be very welcome as im struggling to grasp this
|||Bob,
Thank you very much, that has showed me a lot and i have managed to adapt it
to the nomal xml files that im currently recieving, however, and theres
always a however, some of the files have more than one element of the ssame
name, so in the file i have shown earlier, how would i handle it if say it
had three AddresseeInformation for example
thansk in advance
"Bob Beauchemin" wrote:

> Hi Peter,
> I've made up an really simple table definition for table1 and table2 and
> left some of the columns out to cut down on size. You want to decompose the
> XML into relational name-value pairs and do an insert of pieces into two
> separate tables. This is *assuming* you don't have more than one Header or
> AddresseeInformation in the document, or more than one document in the XML.
> Example follows mail message.
> First way is the easiest. Use the xml.value method to extract each value,
> given the attribute name in the document, 1 column per column in the rowset
> (table) you want. This may be able to be optimized by changing the query,
> but I'm trying to keep it simple for exposition.
> Second way is to use xml.nodes method to obtain a rowset of name-value
> pairs. Then use the PIVOT operator to pivot the values into one row and
> multiple column values. I've done it in two steps (#temp table) to
> illustrate what nodes returns, then combined it into one step.
> You could also use OpenXML to do this, but it might require more storage
> overhead.
> Hope this helps,
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
> CREATE TABLE table1 (
> reporttype varchar(100),
> adviceNumber varchar(100),
> currentProcessingDate datetime
> )
> go
> CREATE TABLE table2 (
> name varchar(100),
> address1 varchar(100),
> address2 varchar(100)
> )
> go
> declare @.x xml
> set @.x =
> '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument>
> <Data>
> <ARUCS>
> <Header reportType="REFT1027" adviceNumber="01077"
> currentProcessingDate="2005-12-05"></Header>
> <AddresseeInformation name="Mr Bean "
> address1="Company Name " address2="This Place "
> address3="This Town " address4="This County
> " address5="A12 45T "></AddresseeInformation>
> </ARUCS>
> </Data>
> <SignatureMethod></SignatureMethod>
> <Signature></Signature>
> </BACSDocument>
> '
> /* First way, using xml.value
> INSERT table1
> select @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.reportType',
> 'varchar(100)') as a,
> @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.adviceNumber',
> 'varchar(100)') as b,
> @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.currentProcessingDate',
> 'datetime') as c
> SELECT * FROM table1
> -- now do the same for table2
> INSERT table2
> select @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.name',
> 'varchar(100)') as a,
> @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address1',
> 'varchar(100)') as b,
> @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address2',
> 'varchar(100)') as c
> SELECT * FROM table2
> */
>
> /* Second way, using xml.nodes, intermediate table for exposition
> select t.c.value('local-name(.)', 'varchar(50)') as Name,
> t.c.value('data(.)', 'varchar(100)') as Value
> into #temp
> from @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
> select * from #temp
> SELECT [reportType], [adviceNumber], [currentProcessingDate]
> FROM #temp
> PIVOT (
> MAX([Value]) FOR
> [Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
> ) as p
> -- now do the same for table2 (elided)
> */
> -- third way, combination of second way into one statement.
> insert table1
> select [reportType], [adviceNumber], [currentProcessingDate]
> from
> (
> SELECT t.c.value('local-name(.)', 'varchar(50)') as [Name],
> t.c.value('data(.)', 'varchar(100)') as [Value]
> FROM @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
> ) AS namevalue
> PIVOT (
> MAX([Value]) FOR
> [Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
> ) as p
> -- now do the same for table2 (elided)
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:E8722036-4DD5-4F15-A4FB-6CF9BF9044EF@.microsoft.com...
>
>
|||Hi Peter,
Those examples showed how to decompose arbitrary XML in multiple unrealated
tables. In a relational database you need to have something tying together
table1 and table2 (adviseNumber?, reportType?). There's nothing in the
document to deduce this. Also, relational doesn't allow repeating groups, so
you'd need a discriminator to distinguish between the 3 nodes. You could
either use ordinal (as I used [1] in the first example to indicate the 1st
AddresseeInformation) or use nodes to do it in one step and insert multiple
rows. But there has to be something in the relational schema tying table1
and table2 together.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3D443871-A973-4407-96EE-5E44EA309BBF@.microsoft.com...[vbcol=seagreen]
> Bob,
> Thank you very much, that has showed me a lot and i have managed to adapt
> it
> to the nomal xml files that im currently recieving, however, and theres
> always a however, some of the files have more than one element of the
> ssame
> name, so in the file i have shown earlier, how would i handle it if say
> it
> had three AddresseeInformation for example
> thansk in advance
> "Bob Beauchemin" wrote:
|||Bob,
Ive spent he entire day chasing ghosts tying to assing a discriminator , its
easy to code when i know how meny there will be but as these reports are
dynamic i need to find a way to change rowcount to equal the ordinal, and
loop till all the rows have been imported
SELECT t.c.value('local-name(.)', 'varchar(50)') AS Name,
t.c.value('data(.)', 'Varchar(100)') as Value, '1' as RowNumber
INTO #ReturnedItem
FROM
@.XMLDOC.nodes('/BACSDocument/Data/ARUCS/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedCreditItem[1]/@.*') as t(c)
youve been such a great help so far, think one i can resolve this issue i
can carry on on my own
"Bob Beauchemin" wrote:

> Hi Peter,
> Those examples showed how to decompose arbitrary XML in multiple unrealated
> tables. In a relational database you need to have something tying together
> table1 and table2 (adviseNumber?, reportType?). There's nothing in the
> document to deduce this. Also, relational doesn't allow repeating groups, so
> you'd need a discriminator to distinguish between the 3 nodes. You could
> either use ordinal (as I used [1] in the first example to indicate the 1st
> AddresseeInformation) or use nodes to do it in one step and insert multiple
> rows. But there has to be something in the relational schema tying table1
> and table2 together.
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:3D443871-A973-4407-96EE-5E44EA309BBF@.microsoft.com...
>
>
|||If I think I'm understanding what you're asking, there's a few ways to do
this. You could add a gratuitous identity column to #temp and use
INSERT...SELECT instead of SELECT INTO. You could loop using a T-SQL
variable until the nodes function returns no nodes, using sql:variable in
the XQuery predicate. You could have also changed the XPath expression to a
FLWOR expression and selected the position, but SQL Server XQuery doesn't
support the "at" portion of "for $x at $y in ..." syntax or the position()
function used outside of the predicate.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:F327D2A5-07D4-4F37-82BA-23B4BEA60226@.microsoft.com...[vbcol=seagreen]
> Bob,
> Ive spent he entire day chasing ghosts tying to assing a discriminator ,
> its
> easy to code when i know how meny there will be but as these reports are
> dynamic i need to find a way to change rowcount to equal the ordinal, and
> loop till all the rows have been imported
> SELECT t.c.value('local-name(.)', 'varchar(50)') AS Name,
> t.c.value('data(.)', 'Varchar(100)') as Value, '1' as RowNumber
> INTO #ReturnedItem
> FROM
> @.XMLDOC.nodes('/BACSDocument/Data/ARUCS/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedCreditItem[1]/@.*')
> as t(c)
>
> youve been such a great help so far, think one i can resolve this issue i
> can carry on on my own
> "Bob Beauchemin" wrote:
|||Bob,
Thanks again for the pointers yhoi have to admit ive spend a few hours and
srtill carnt grasp it. I understand what you were saying about the
relationship between table 1 and table 2, that i think i can work out, what
im still struggling with is returning the pivot table for the three addresss
( only for example ). I still can not get the identity colum in using your
previous eamaples. sorry to be a pain but can you pint me in the direction
of an example based of what you have already explained
thanks again for all your help
"Bob Beauchemin" wrote:

> If I think I'm understanding what you're asking, there's a few ways to do
> this. You could add a gratuitous identity column to #temp and use
> INSERT...SELECT instead of SELECT INTO. You could loop using a T-SQL
> variable until the nodes function returns no nodes, using sql:variable in
> the XQuery predicate. You could have also changed the XPath expression to a
> FLWOR expression and selected the position, but SQL Server XQuery doesn't
> support the "at" portion of "for $x at $y in ..." syntax or the position()
> function used outside of the predicate.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:F327D2A5-07D4-4F37-82BA-23B4BEA60226@.microsoft.com...
>
>

help needed on XML

using the BOL and looking through some of the posts on here ive managed to
come up with the following code in T-SQl
DECLARE @.TESTXML varchar(8000)
SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
<BDocument>,
<Data>
<InputReport>
<Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
reportSequenceNumber="000121" userNumber="123456">
<ProducedOn time="19:21:22" date="2004-09-30"/>
<ProcessingDate date="2004-10-01"/>
</Header>
</InputReport>
</Data>
</BDocument>'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
SELECT *
FROM
OPENXML(@.hDoc, '/BDocument')
EXEC sp_xml_removedocument @.hDoc
I need help on expanding this.
1. Would i be right in assuming that to load the contants of a XML file
into the variable @.TESTXML, i would need to use something like actixex in a
DTS.
2. how can i in this case just do a select on a specific field ie
'reportnumber'
3. some of the reports i will be recieving will have the same field names in
different sections, for example
- <AccountTotals>
- <DebitEntry>
<AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
</DebitEntry>
</AccountTotal>
- <CreditEntry>
<AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
<AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
</CreditEntry>
As you can see the field 'numberOf' is used several times, how can i
differanciate between each one in each section
See below.
Best regards
Michael
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3A396C18-0271-4DD4-B2B5-C74505CE1156@.microsoft.com...
> using the BOL and looking through some of the posts on here ive managed to
> come up with the following code in T-SQl
> DECLARE @.TESTXML varchar(8000)
> SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BDocument>,
> <Data>
> <InputReport>
> <Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
> reportSequenceNumber="000121" userNumber="123456">
> <ProducedOn time="19:21:22" date="2004-09-30"/>
> <ProcessingDate date="2004-10-01"/>
> </Header>
> </InputReport>
> </Data>
> </BDocument>'
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> SELECT *
> FROM
> OPENXML(@.hDoc, '/BDocument')
> EXEC sp_xml_removedocument @.hDoc
> I need help on expanding this.
> 1. Would i be right in assuming that to load the contants of a XML file
> into the variable @.TESTXML, i would need to use something like actixex in
> a
> DTS.
Not necessarily. Any client side API that allows you to pass a parameter to
a stored proc should work. Just copy the file content over as parameter
value.

> 2. how can i in this case just do a select on a specific field ie
> 'reportnumber'
The OpenXML above results in an edge table.
To get the reportNumber for every header, you would replace your select
with:
select *
from OpenXML(@.hDoc, '/BDocument/Data/InputReport/Header') WITH (rno int
'@.reportNumber')

> 3. some of the reports i will be recieving will have the same field names
> in
> different sections, for example
> - <AccountTotals>
> - <DebitEntry>
> <AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
> </DebitEntry>
> </AccountTotal>
> - <CreditEntry>
> <AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
> <AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
> </CreditEntry>
>
> As you can see the field 'numberOf' is used several times, how can i
> differanciate between each one in each section
The following will give you only AcceptedRecords:
select * from OpenXML(@.hDoc, '//AcceptedRecords') with
(numberOf int, valueOf real, currency nvarchar(5))
The following will give you numberOf and the name of its element:
select * from OpenXML(@.hDoc, '//DebitEntry/*') with
(recname nvarchar(40) '@.mp:localname', numberOf int)
HTH
Michael