Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts

Sunday, March 11, 2012

Another problem with XQuery!

Hi all,

In my Sql Server 2005 database I have a column called OtherInformation which is an XML datatype. Within this column I have XML in the following format:

<Logs>

<Event Id="123">

<Information>Database crash</Information>

</Event>

</Logs>

The value of the Id attribute can change but I an trying to write a query that will retrieve the value of the Information element when the Id attribute of the Event element is 123. What I have so far is below but this returns the text regardless of what the attributes value is!

SELECT

LogId, OtherInformation.query('Logs/Event/Information/text()') As EventInformation

FROM

Logs

WHERE

OtherInformation.exist('Logs/Event[@.Id]') = '123'

Any help on this would be great. Thanks!

You need this query:

Code Snippet

SELECT LogId, OtherInformation.query('Logs/Event/Information/text()')

FROM Logs

WHERE OtherInformation.exist('Logs/Event[@.Id = "123"]') = 1;

|||Fantastic Martin. That works great!

Friday, February 24, 2012

annotated schema and bulk loading into multiple tables...

Hi guys,
I'm trying to get XML data loaded into a set of tables using bulk load. The
child tables also have an XML column where I want to store portions of the
XML.
So far the I've got it correctly inserting data into the parent, and able to
insert the correct amount of rows in the child tables, but the data in the
child tables is empty... The child data is an identity column, a foreign key
pointing back to the parent row (empty!), and an XML data column holding the
contents of the xml fragment (also empty!).
Any help would be greatly appreciated!
Thanks.
Daniel.
Below is where I've got to so far...
SQL definitions for my parent table and a child table:
----
CREATE TABLE [dbo].[Foo](
[Foo_PK] [int] IDENTITY(1,1) NOT NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
(
[Foo_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ChildOfFoo](
[ChildOfFoo_PK] [int] IDENTITY(1,1) NOT NULL,
[Foo_FK] [int] NULL,
[Data] [xml] NULL,
CONSTRAINT [PK_ChildOfFoo] PRIMARY KEY CLUSTERED
(
[ChildOfFoo_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here's the annotated XSD:
----
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<!-- RELATIONSHIP FOR CONNECTING THE CHILD KEY TO THE PARENT
INDEX -->
<sql:relationship name="HeaderWash"
parent="Foo"
parent-key="Foo_PK"
child="ChildOfFoo"
child-key="Foo_FK" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<!-- HEADER XML TO FOO TABLE -->
<xs:element name="Header"
sql:relation="Foo"
sql:key-fields="Foo_PK">
<xs:complexType>
<xs:sequence>
<xs:element name="Foo_PK" type="xs:integer"
minOccurs="0"
default="0" sql:identity="ignore"/>
<xs:element name="StartDateTime"
type="xs:dateTime" />
<xs:element name="EndDateTime"
type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- CHILD XML TO CHILD OF FOO TABLE -->
<xs:element name="Children" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Wash"
sql:relation="ChildOfFoo"
sql:key-fields="ChildOfFoo_PK"
sql:relationship="HeaderWash">
<xs:complexType>
<xs:sequence>
<xs:element name="ChildOfFoo_PK"
type="xs:string"
minOccurs="0"
default="0" sql:identity="ignore" />
<xs:element name="Foo_FK"
type="xs:string" />
<xs:element name="Wash"
sql:field="Data" sql:datatype="xml" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
And finally, (thanks for your patience!), a sample of XML data...
----
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Root>
<Header>
<StartDateTime>15 Jun 2007 08:07:00</StartDateTime>
<EndDateTime>15 Jun 2007 09:12:00</EndDateTime>
</Header>
<Children>
<!-- EACH CHILDOFFOO CREATE'S A ROW IN CHILDOFFOO TABLE
AND ALSO IS INSERTED INTO DATA XML COLUMN.
CAN CONTAIN <ChildOFFoo>, BUT BETTER TO JUST HAVE
INNER TEXT XML FRAGMENT...
-->
<ChildOfFoo>
<Phasetime>02:00</Phasetime>
<Heated>No</Heated>
</ChildOfFoo>
<ChildOfFoo>
<Quantityinjected>22.8 ml</Quantityinjected>
<Lowerlimit>61.1 ml</Lowerlimit>
<Upperlimit>82.7 ml</Upperlimit>
</ChildOfFoo>
<ChildOfFoo>
<Watertype>Cold water</Watertype>
<Numberofstages>1</Numberofstages>
</ChildOfFoo>
<ChildOfFoo>
<Quantityinjected>137.6 ml</Quantityinjected>
<Lowerlimit>122.3 ml</Lowerlimit>
<Upperlimit>165.4 ml</Upperlimit>
</ChildOfFoo>
<ChildOfFoo>
<Circulationpressure>Detected</Circulationpressure>
</ChildOfFoo>
<ChildOfFoo>
<Heated>No</Heated>
<Circulationpressure>Detected</Circulationpressure>
</ChildOfFoo>
</Children>
</Root>In case anyone elase wants this solution... The schema is modified as such:
1. When referencing a foreign key (as in the relationship) it needs to stay
in scope from when it is initially populated by the parent table, to each
time it is used. If you get a foreign key of null, you are most probably
using the FK out of scope.
2. Use the sql:overflow-field="<MyXmlColumn>" in the element that relates to
the table you want to store as XML. The sql:overflow-field field takes all
unreferenced XML in that node and stuffs it into the field you specify.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<!-- RELATIONSHIP FOR CONNECTING THE CHILD KEY TO THE PARENT
INDEX -->
<sql:relationship name="HeaderWash"
parent="Foo"
parent-key="Foo_PK"
child="ChildOfFoo"
child-key="Foo_FK" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<!-- HEADER XML ELEMENTS TO FOO TABLE -->
<xs:element name="Foo_PK" type="xs:integer" minOccurs="0"
default="0" sql:identity="ignore"/>
<xs:element name="StartDateTime" type="xs:dateTime" />
<xs:element name="EndDateTime" type="xs:dateTime" />
<!-- CHILD XML TO CHILD OF FOO TABLE -->
<xs:element name="Children" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Wash"
sql:relation="ChildOfFoo"
sql:key-fields="ChildOfFoo_PK"
sql:relationship="HeaderWash"
sql:overflow-field="Data">
<xs:complexType>
<xs:sequence>
<xs:element name="ChildOfFoo_PK"
type="xs:string"
minOccurs="0" default="0"
sql:identity="ignore" />
<xs:element name="Foo_FK"
type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:annotation>
> <xs:appinfo>
> <!-- RELATIONSHIP FOR CONNECTING THE CHILD KEY TO THE PARENT
> INDEX -->
> <sql:relationship name="HeaderWash"
> parent="Foo"
> parent-key="Foo_PK"
> child="ChildOfFoo"
> child-key="Foo_FK" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Root" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <!-- HEADER XML TO FOO TABLE -->
> <xs:element name="Header"
> sql:relation="Foo"
> sql:key-fields="Foo_PK">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Foo_PK" type="xs:integer"
> minOccurs="0"
> default="0" sql:identity="ignore"/>
> <xs:element name="StartDateTime"
> type="xs:dateTime" />
> <xs:element name="EndDateTime"
> type="xs:dateTime" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <!-- CHILD XML TO CHILD OF FOO TABLE -->
> <xs:element name="Children" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element maxOccurs="unbounded" name="Wash"
> sql:relation="ChildOfFoo"
> sql:key-fields="ChildOfFoo_PK"
> sql:relationship="HeaderWash">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ChildOfFoo_PK"
> type="xs:string"
> minOccurs="0"
> default="0" sql:identity="ignore" />
> <xs:element name="Foo_FK"
> type="xs:string" />
> <xs:element name="Wash"
> sql:field="Data" sql:datatype="xml" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:%23gNnsRHxHHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> I'm trying to get XML data loaded into a set of tables using bulk load.
> The child tables also have an XML column where I want to store portions of
> the XML.
> So far the I've got it correctly inserting data into the parent, and able
> to insert the correct amount of rows in the child tables, but the data in
> the child tables is empty... The child data is an identity column, a
> foreign key pointing back to the parent row (empty!), and an XML data
> column holding the contents of the xml fragment (also empty!).
> Any help would be greatly appreciated!
> Thanks.
> Daniel.
>
>
> Below is where I've got to so far...
> SQL definitions for my parent table and a child table:
> ----
> CREATE TABLE [dbo].[Foo](
> [Foo_PK] [int] IDENTITY(1,1) NOT NULL,
> [StartDateTime] [datetime] NULL,
> [EndDateTime] [datetime] NULL,
> CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
> (
> [Foo_PK] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[ChildOfFoo](
> [ChildOfFoo_PK] [int] IDENTITY(1,1) NOT NULL,
> [Foo_FK] [int] NULL,
> [Data] [xml] NULL,
> CONSTRAINT [PK_ChildOfFoo] PRIMARY KEY CLUSTERED
> (
> [ChildOfFoo_PK] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> Here's the annotated XSD:
> ----
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:annotation>
> <xs:appinfo>
> <!-- RELATIONSHIP FOR CONNECTING THE CHILD KEY TO THE PARENT
> INDEX -->
> <sql:relationship name="HeaderWash"
> parent="Foo"
> parent-key="Foo_PK"
> child="ChildOfFoo"
> child-key="Foo_FK" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Root" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <!-- HEADER XML TO FOO TABLE -->
> <xs:element name="Header"
> sql:relation="Foo"
> sql:key-fields="Foo_PK">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Foo_PK" type="xs:integer"
> minOccurs="0"
> default="0" sql:identity="ignore"/>
> <xs:element name="StartDateTime"
> type="xs:dateTime" />
> <xs:element name="EndDateTime"
> type="xs:dateTime" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <!-- CHILD XML TO CHILD OF FOO TABLE -->
> <xs:element name="Children" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element maxOccurs="unbounded" name="Wash"
> sql:relation="ChildOfFoo"
> sql:key-fields="ChildOfFoo_PK"
> sql:relationship="HeaderWash">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ChildOfFoo_PK"
> type="xs:string"
> minOccurs="0"
> default="0" sql:identity="ignore" />
> <xs:element name="Foo_FK"
> type="xs:string" />
> <xs:element name="Wash"
> sql:field="Data" sql:datatype="xml" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
>
> And finally, (thanks for your patience!), a sample of XML data...
> ----
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <Root>
> <Header>
> <StartDateTime>15 Jun 2007 08:07:00</StartDateTime>
> <EndDateTime>15 Jun 2007 09:12:00</EndDateTime>
> </Header>
> <Children>
> <!-- EACH CHILDOFFOO CREATE'S A ROW IN CHILDOFFOO TABLE
> AND ALSO IS INSERTED INTO DATA XML COLUMN.
> CAN CONTAIN <ChildOFFoo>, BUT BETTER TO JUST HAVE
> INNER TEXT XML FRAGMENT...
> -->
> <ChildOfFoo>
> <Phasetime>02:00</Phasetime>
> <Heated>No</Heated>
> </ChildOfFoo>
> <ChildOfFoo>
> <Quantityinjected>22.8 ml</Quantityinjected>
> <Lowerlimit>61.1 ml</Lowerlimit>
> <Upperlimit>82.7 ml</Upperlimit>
> </ChildOfFoo>
> <ChildOfFoo>
> <Watertype>Cold water</Watertype>
> <Numberofstages>1</Numberofstages>
> </ChildOfFoo>
> <ChildOfFoo>
> <Quantityinjected>137.6 ml</Quantityinjected>
> <Lowerlimit>122.3 ml</Lowerlimit>
> <Upperlimit>165.4 ml</Upperlimit>
> </ChildOfFoo>
> <ChildOfFoo>
> <Circulationpressure>Detected</Circulationpressure>
> </ChildOfFoo>
> <ChildOfFoo>
> <Heated>No</Heated>
> <Circulationpressure>Detected</Circulationpressure>
> </ChildOfFoo>
> </Children>
> </Root>
>

Annotated Mapping Schema

I have a stored procedure that uses the FOR XML EXPLICIT mode to return an xml view of my data. I then insert this xml into another table.
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like to use an xsd schema, however I can't find examples that let you apply the transformation in a stored procedure / test it in Query Analyzer. All the examples I've found involve m
apping the schema in the URL or using a SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul
Mapping Schemas are a client-side technology - actually, they just generate
FOR XML EXPLICIT statements on the server (you can see this by running a
trace when retrieving data with a schema). As such, there's no way to
reference them from within a T-SQL sproc. Sorry!
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Paul Bibby" <Paul Bibby@.discussions.microsoft.com> wrote in message
news:4B7E0882-5EED-4A7F-987D-125262683276@.microsoft.com...
I have a stored procedure that uses the FOR XML EXPLICIT mode to return an
xml view of my data. I then insert this xml into another table.
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like
to use an xsd schema, however I can't find examples that let you apply the
transformation in a stored procedure / test it in Query Analyzer. All the
examples I've found involve mapping the schema in the URL or using a
SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul

Thursday, February 9, 2012

Analysis Services 2005 Deployment Wizard

Hi,
I used the deployment wizard in the analysis services 2005 to convert
the cube into XML script. Using the SQL Management Studio I opened it
as Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query. What has to be done after this in order to use it as a
production server?
Thanks,
Somu
Hi,
I was able to connect with a local machine but while synchronising the
dev and production servers i am getting an error "peer prematurely
closed the conn" , " error was encountered in the transport layer" Wat
should i do to over come this...
Thanks,
SOMU

Analysis Services 2005 Deployment Wizard

Hi,
I used the deployment wizard in the analysis services 2005 to convert
it into XML script. Using the SQL Management Studio I opened it as
Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query. What has to be done after this in order to use it as a
production server?
Thanks,
SomuHi,
I was able to connect with a local machine but while synchronising the
dev and production servers i am getting an error "peer prematurely
closed the conn" , " error was encountered in the transport layer" Wat
should i do to over come this...
Thanks,
SOMU|||Hi,
I was able to connect with a local machine but while synchronising the
dev and production servers i am getting an error "peer prematurely
closed the conn" , " error was encountered in the transport layer" Wat
should i do to over come this...
Thanks,
SOMU

Analysis Services 2005 Deployment Wizard

Hi,
I used the deployment wizard in the analysis services 2005 to convert
the cube into XML script. Using the SQL Management Studio I opened it
as Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query. What has to be done after this in order to use it as a
production server?
Thanks,
SomuHi,
I was able to connect with a local machine but while synchronising the
dev and production servers i am getting an error "peer prematurely
closed the conn" , " error was encountered in the transport layer" Wat
should i do to over come this...
Thanks,
SOMU