SQL Server XML Forum

Trim on XML BulkLoad

Sun, 29 Nov 2009 04:41:15 Z

Is it possible to trim on xml bulkload, what I'm wanting to do is remove the first character from the STLMT_TYP_CD variable, example(S7 or S14- what i want to do is remove the S and just have the 7 or 14 left over. Is this possible on bulkload.

DECLARE @xml XML

SELECT @xml = x.y
FROM OPENROWSET( BULK 'c:\temp\temp.xml', SINGLE_BLOB ) x(y)

--INSERT INTO ...
SELECT *
FROM
	(
	SELECT
stlmt_typ.x.value('STLMT_TYP_CD[1]', 'VARCHAR(30)') AS STLMT_TYP_CD
FROM @xml.nodes('SUMMARY') AS summary(x)
) x

Does T-SQL has any problem handling/generating large xmls ?

Mon, 23 Nov 2009 15:24:21 Z

Does T-SQL has any problem handling/generating large xmls ?

Getting NULL output when due to data, i am sure to get the xml output from select query.

Any reason ?

Some of procedure were returning xml. Valid data is available but suddenly they stop giving the output in SSMS. Unable to understand the reason.

Think BIG but Positive, may be GLOBAL better UNIVERSAL.

Parsing xml into sql

Wed, 25 Nov 2009 20:51:19 Z

Ok, heres my issue, besides still being a newbie to programming. I need to import the information from a xml document into a sql server 2005 database. I have spent the last week searching the web trying to figure out how to do this. From what I can tell I should either use a T-Sql statement or SQL Bulk Copy i'm using vb.net 2008 Pro as the application front end. Here is the Schema and a small part of the xml document.

xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="SUMMARY">
<xs:complexType>
<xs:sequence>
<xs:element name="NAME" type="xs:string" />
<xs:element name="ID" type="xs:string" />
<xs:element name="TIMESTAMP" type="xs:string" />
<xs:element name="SCHEDULED_DATE" type="xs:string" />
<xs:element name="STATEMENT_ID" type="xs:string" />
<xs:element name="LINE_ITEMS">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="CHG_TYP">
<xs:complexType><xs:sequence>
<xs:element name="CHG_TYP_ID" type="xs:string" />
<xs:element name="CHG_TYP_NM" type="xs:string" />
<xs:element maxOccurs="unbounded" name="STLMT_TYP">
<xs:complexType>
<xs:sequence>
<xs:element name="STLMT_TYP_CD">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="INVOICE" type="xs:string" use="required" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="OPERATING_DATE" type="xs:string" />
<xs:element name="AMT" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="INV_TOTAL">
<xs:complexType>
<xs:sequence>
<xs:element name="AMT" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Here is an section of the xml document.

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="http://ABC.org/vss/SettlementSummary.xsl"?>
<SUMMARY><NAME>DELL</NAME><ID>1-3L0WU</ID><TIMESTAMP>11/02/2009</TIMESTAMP><SCHEDULED_DATE>11/03/2009</SCHEDULED_DATE><STATEMENT_ID>AO-SUMM_ABC_11032009</STATEMENT_ID><LINE_ITEMS><CHG_TYP><CHG_TYP_ID>DA_ADMIN</CHG_TYP_ID><CHG_TYP_NM>DA Market Admin Amount</CHG_TYP_NM><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S7</STLMT_TYP_CD><OPERATING_DATE>10/20/2009</OPERATING_DATE><AMT>1595.50</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="N">S7</STLMT_TYP_CD><OPERATING_DATE>10/27/2009</OPERATING_DATE><AMT>1595.63</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S14</STLMT_TYP_CD><OPERATING_DATE>10/20/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S55</STLMT_TYP_CD><OPERATING_DATE>09/09/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S105</STLMT_TYP_CD><OPERATING_DATE>07/21/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><INV_TOTAL><AMT>1595.50</AMT></INV_TOTAL></CHG_TYP><CHG_TYP><CHG_TYP_ID>DA_ASM_REG</CHG_TYP_ID><CHG_TYP_NM
>RT Virt En Amt</CHG_TYP_NM><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S7</STLMT_TYP_CD><OPERATING_DATE>10/20/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="N">S7</STLMT_TYP_CD><OPERATING_DATE>10/27/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S14</STLMT_TYP_CD><OPERATING_DATE>10/20/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S55</STLMT_TYP_CD><OPERATING_DATE>09/09/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><STLMT_TYP><STLMT_TYP_CD INVOICE="Y">S105</STLMT_TYP_CD><OPERATING_DATE>07/21/2009</OPERATING_DATE><AMT>0.00</AMT></STLMT_TYP><INV_TOTAL><AMT>0.00</AMT></INV_TOTAL></CHG_TYP></LINE_ITEMS></SUMMARY>

What I need to import to do is grab the <Name>ELEMENT, <SCHEDULED_DATE>ELEMENT, ALL OF THE <STLMT_TYP_CD>ELEMENTS AND ATTRIBUTES, the <AMT>by it's CHG_TYP_ID.

So basically what would be imported into the SQL table would be:

Name             Scheduled Date               STLMT_TYP_CD           INVOICE           OPERATING DATE              DA_ADMIN               RT VIRT EN AMT
DELL                11/03/2009                           S7                          Y                  10/20/2009                    1595.50                        0
DELL                11/03/2009                           S7                          N                  10/27/2009                    1595.63                        0
DELL                11/03/2009                           S14                        Y                  10/20/2009                              0                        0
DELL                11/03/2009                           S55                        Y                   9/09/2009                               0                        0
DELL                11/03/2009                           S105                      Y                   7/21/2009                               0                        0

I know what I want however I have no clue on how to right it.

Any assistance would be greatly welcomed.

Terry

Retrieving large xml docuements from columns of type 'xml' in SQL Server 2005

Fri, 27 Nov 2009 12:57:26 Z

Hi Friends,

I am working on one of the MSIT projects and need help in solving the following issue.

Our database contains a table with columns of xml type. These xml columns contain large xml documents.

For example: A Single row in the table is as below.

GuId

License Info

Profile Info

Transaction Info

Product Info

G01

<License…..>

------

</License>

<Profile….

…….

</Profile>

<Transactions….>

………..

………

………..

</Transactions>

<Product ….

……..

</Product>

Above 4 columns contains large xml documents.

This table is filled by another stored procedure, which collects data from multiple tables and forms xml documents (using ‘for xml’ in select query) and inserts into the above table. So the size of the xml documents is not fixed.

Table definition is as below:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Requests](

[ID] [bigint] NOT NULL,

[GUID] [uniqueidentifier] NOT NULL,

[License Info] xml NULL,

[Profile Info] xml NULL,

[Transaction Info] xml NULL,

[Product Info] xml NULL,

CONSTRAINT [PK_Requests] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

‘ID’ has primary key as well as clustered index

‘GuId’ has non clustered index.

Database: SQL Server 2005

Front-End: .Net ( WCF) 2005/ 2008

Language: C#

Requirement:

Our WCF has to fetch the above 4 xml documents from the database table. And we have to return this to client application. We are not displaying that data anywhere; our job Is just pull the data from the database and pass it to the client applications (Web/ Windows).

We will develop the client app later. Based upon the output from the WCF (DataSet/ Xml/ Object) the methods in the client application will be developed.

Problem:

When the xml columns contain a huge xml document, even simple select query is also taking approximately 25 minutes to retrieve the data in SQL Server Management Studio.

Always we will fetch the data by passing the Request Id only.

Ex: Select * from Request (NoLock) where Id = 1

Need help in:

How to retrieve the data from the above table as fast as possible, irrespective of the size of the xml document in those columns?

After retrieving the data from the database, what is the best way/ format that our WCF will use to pass it back to the client application?

Please respond with your suggestions as early as possible.

Thanks,

Madhu

operation in xml.modify made conditional

Fri, 27 Nov 2009 10:10:29 Z

i have a situation in which i want to modify a node with the condition of replacing the oldval with nw val if the new val is already not present
so here i have to do 2 query
1)replacing on the basis of filtering that newval sohould not exist
2)delete node with old value

so i have to make 2 query ,can't i make this condion in the single query like
xml.modify('if(new val exist)
then (delete old valnode)
else (replace value of oldval with newval)'

is it feasible as running 2 queryis taking lot of time

web developer

maxlength="*" query

Wed, 25 Nov 2009 17:40:45 Z

i'd like to know how does one limit the number of characters a site visitor is allowed to input on a textarea just like one would code the style of a textbox area for entering an e-mail adress as maxlength="30" for 30 characters as a validation?

Retrieving large xml docuements from columns of type 'xml' in SQL Server 2005

Fri, 27 Nov 2009 06:51:30 Z

Hi Friends,
I am working on one of the MSIT projects and need your help in solving the following issue.

Our database contains a table with columns of xml type. These xml columns contain large xml documents.

For example: A Single row in the table is as below.

GuId

License Info

Profile Info

Transaction Info

Product Info

G01

<License…..>

------

</License>

<Profile….

…….

</Profile>

<Transactions….>

………..

………

………..

</Transactions>

<Product ….

……..

</Product>

Above 4 columns contains large xml documents.

Table definition is as below:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Requests](

[ID] [bigint] NOT NULL,

[GUID] [uniqueidentifier] NOT NULL,

[License Info] xml NULL,

[Profile Info] xml NULL,

[Transaction Info] xml NULL,

[Product Info] xml NULL,

CONSTRAINT [PK_Requests] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

‘ID’ has primary key as well as clustered index

‘GuId’ has non clustered index.

Database: SQL Server 2005

Front-End: .Net ( WCF) 2005/ 2008

Language: C#

Requirement:

We will develop the client app later. Based upon the output from the WCF (DataSet/ Xml/ Object) the methods in the client application will be developed.

Problem:

Always we will fetch the data by passing the Request Id only.

When the xml columns contain a huge xml document, even simple select query is also taking approximately 25 minutes to retrieve the data in SQL Server Management Studio.

Ex: Select * from Request (NoLock) where Id = 1

Need help in:

How to retrieve the data from the above table as fast as possible, irrespective of the size of the xml document in those columns?

After retrieving the data from the database, what is the best way/ format that our WCF will pass it back to the client application?

Please respond with your suggestions as early as possible.

Thanks,
Madhu

Need help with XQuery

Fri, 27 Nov 2009 02:49:18 Z

I have 2 xml documents Team and Player

Here is how they look like

team.xml


 


<Teams>


<Team>


<Lost>59</Lost>


<Name>NewYork</Name>


<Won>103</Won>


</Team>


<Team>


..


..


</Team>


..


..


<Teams>







player.xml

 

<Players>

  <Player>

  <Age>24</Age>

  <Team>NewYork</Team>

  <BOB>67</BOB>

  <SOT>265</SOT>

  <IPT>229.3</IPT>

  </Player>

  <Player>

  ..

  ..

  </Player>

  ..

  ..

<Players>




 I want to write a xquery to dsiplay for each team; the number of good*
 players, the avg age of good players, the number of total players and
 avg age of total players.
 
 
 
 The criteria for a good player is (BOB + SOT) / IPT < 0.91.
 
 
 
 This what I tried
 

for $x in distinct-values (doc("team.xml")/Teams/Team/Team)
let $y:= doc("player.xml")/Players/Player[Team=$x]
 
return
<Team>
	<Team_Name>{data($x)}</Team_Name>
	<Total_players>{count($y)}</Total_players>
	<Average_Age>{avg($y/Age)}</Average_Age>
</Team>


 Using this I get the the number of total players and avg age of total
 players. However, i'm not sure how I can modify this to return the
 information about good players as well.
 
 
 
 Can someone please help me with this.
 
 
 
 Thanks.

Value and nodes method to retrieve multiple rows from XML fragment

Thu, 26 Nov 2009 01:14:16 Z

If I do this:

declare @xml xml
set @xml =
'<root>
<fn>1</fn>
<fn>3</fn>
</root>'

SELECT List.Myfn.value('(/root/fn)[1]', 'int') AS MyValue
from @xml.nodes('(/root/fn)') AS List(Myfn)

I get this as a return:

value
1
1

This is what I want to return:

value
1
3

What am I doing wrong?

Thank you,

Alain Quesnel

create xml index without primary key clustered present

Thu, 26 Nov 2009 06:03:59 Z

i have tables that have clustered index not on their primary keys ,
and need to do xml query on xml field of those tables,
query include basically exist,modify (insert update delete).
and it is taking lot of time so finding no way to decrease the execution time

web developer

Problem in Parsing AMAZON XML Using Openxml

Sun, 22 Nov 2009 07:11:54 Z

Hello Everybody,
I am trying to parse the below xml document using openxml in sqlserver 2005. Everything is fine except the features tag. I am trying to get all the features nodes of itemattributes node in one xml column(for ex. <features><feature>1</feature><feature>2</feature></features>) but I am getting only one at a time. Let me show you the tsql script that I am executing-

Source

DECLARE @xmlData XML;

set @xmlData = '<?xml version="1.0"?>
<ItemSearchResponse xmlns="http://webservices.amazon.com/AWSECommerceService/2005-10-05">
    <Items>
        <Item>
        <ASIN>B001MIZNEM</ASIN>
        <DetailPageURL>http://www.amazon.com/Cake-Mania-Game-Download-Pc/dp/B001MIZNEM%3FSubscriptionId%3D0Z6TA0JMJ7NM6RQP0WR2%26tag%3Dws%26linkCode%3Dxm2%26camp%3D2025%26creative%3D165953%26creativeASIN%3DB001MIZNEM</DetailPageURL>
        <SalesRank>69</SalesRank>

            <ItemAttributes>
                <Binding>Software Download</Binding>
                <Brand>Sandlot Games</Brand>
                <Edition>Standard</Edition>
                <Feature>48 Levels Of Baking Fun.</Feature>
                <Feature>Customize And Upgrade Your Kitchen</Feature>
                <Feature>4 Unique Bakeries To Run</Feature>
                <Feature>Hilarious Seasonal Characters.</Feature>
                <Format>Download</Format>
                <Label>Sandlot Games</Label>
            </ItemAttributes>
        </Item>
    </Items>
</ItemSearchResponse>';

Declare @iDoc int
    EXEC sp_xml_preparedocument @iDoc Output,@xmlData,'<ItemSearchResponse xmlns:ns="http://webservices.amazon.com/AWSECommerceService/2005-10-05"/>'


    SELECT ox.*,getdate(),getdate()
    From OPENXML(@iDoc,'/ns:ItemSearchResponse/ns:Items/ns:Item',3)
    With
    (
        [ASIN] nvarchar(50) 'ns:ASIN',
        DetailPageURL nvarchar(2000) 'ns:DetailPageURL',
        Features xml 'ns:ItemAttributes/ns:Feature'

    ) ox

    Exec sp_xml_removedocument @iDoc

Expected Output:

ASIN	DetailedPageURL	Features
B001MIZNEM	http://www.amazon.com/ Cake-Mania-Game-Download- Pc/dp/B001MIZNEM%3F SubscriptionId%3D0Z6TA0JMJ7 NM6RQP0WR2%26tag%3Dws%26 linkCode%3Dxm2%26camp%3D2025 %26creative%3D165953% 26creativeASIN%3DB001MIZNEM	<Features> <Feature>48 Levels Of Baking Fun.</Feature> <Feature>Customize And Upgrade Your Kitchen</Feature> <Feature>4 Unique Bakeries To Run</Feature> <Feature>Hilarious Seasonal Characters.</Feature> </Features>

Can anybody please help me to achieve the above result? Thanks,

XML to SQL

Thu, 07 Feb 2008 10:08:39 Z

Hi All,
I have a problem , I have a XML file from which i want to take data(only one table ) and put into SQL server table
how can i accomplish this...
please send me any sample code .....

I am pasting few lines of my XML doc ..It looks something like this.....

Please help me .....
Thank you...

?xml version="1.0" encoding="iso-8859-1" ?>

<root>
    <doctype>HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"</doctype>
    <html xmlns:webvar="http://www.bloomberg.com/webvar">
        <head>
            <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
            <title>
                <text>Bloomberg.com:
                  Benchmark Currency Rates</text>
            </title>
            <meta name="KEYWORDS" content="currency, rates, dollar, euro, pound, franc, yen, canadian dollar, hong kong dollar, australian dollar, conversion rates, currency strength, charts, exchange rates" />
            <meta content="Bloomberg L.P." name="OWNER" />
            <meta name="ROBOTS" content="NOARCHIVE" />
            <meta name="DESCRIPTION" content="" />
            <meta http-equiv="expires" content="Wed, 06 Feb 2008 11:02:00 GMT" />
            <meta http-equiv="refresh" content="1200" />
            <link media="screen" type="text/css" href="/styles/main2.css" rev="stylesheet" rel="stylesheet" />
            <link media="screen" type="text/css" rev="stylesheet" rel="stylesheet" href="/styles/markets.css" />
            <script language="JavaScript" src="/jscommon/ctype.js" />
            <script language="JavaScript" src="/jscommon/banner.js" />
            <script language="JavaScript" src="/jscommon/dropmenu.js" />
            <script language="JavaScript" src="/jscommon/header_v1.js" />
            <script language="JavaScript" src="/jscommon/flsh_charts.js" />
            <script language="JavaScript" src="/jscommon/flsh_nav.js" />
            <script language="JavaScript" src="/jscommon/bringupPlayer.js" />
        </head>
        <body topmargin="0" leftmargin="0" marginwidth="0" marginheight="0">
            <comment>20602081</comment>
            <div id="content">
                <map name="bba">
                    <area target="_blank" href="https://bba.bloomberg.net/" coords="58,2,217,23" alt="Bloomberg Anywhere" />
                    <area target="_blank" href="http://about.bloomberg.com/software/index.html" coords="220,2,366,23" alt="Software Support" />
                    <area target="_blank" href="http://www.bloomberg.com/apps/fbk" coords="369,2,467,23" alt="Feedback" />
                </map>
                <div id="header">
                    <a href="http://www.bloomberg.com">
                        <img align="left" border="0" alt="Bloomberg" height="51" width="250" src="http://images.bloomberg.com/r06/navigation/logo.gif" />
                    </a>
                    <div id="anywhere">
                        <table cellpadding="0" cellspacing="0" border="0">
                            <tr>
                                <td>
                                    <img border="0" height="24" width="482" alt="Bloomberg Anywhere" src="http://images.bloomberg.com/r06/homepage/bbganywhere2.gif" usemap="#bba" />
                                </td>
                                <td>
                                    <div id="log" />
                                    <script xmlns:webad="http://www.bloomberg.com/webad" xmlns:wn="http://www.bloomberg.com/bloomberg-web-news" xmlns:nav="http://www.bloomberg.com/navigation" language="JavaScript">
                                        <text><![CDATA[x = "x60,x70";
Description = "/markets/currencies/benchmark";
Keys = "null";

AD_INIT(x, Keys, Description);
]]></text>
                                    </script>
                                    <script language="JavaScript">
                                        <text><![CDATA[weblogin();
        ]]></text>
                                    </script>
                                </td>
                            </tr>
                        </table>
                    </div>

FOR XML PATH - Whats the difference between these two queries

Fri, 20 Nov 2009 17:17:34 Z

Hello,

I am in a situation and i have tried to create a similiar situation using northwind database. I just want to understand difference between these query.

Query 1:

USE NORTHWND

GO



select 

	 FirstName as '@FirstName1'

	,(

		select ShipName as '@ShipName'

		from

			dbo.Orders ORD

		where

			ORD.EmployeeID = EMP.EmployeeID

		FOR XML PATH('Ship'), ROOT('Ships'), Type

	)

	,(

		select TerritoryDescription as '@TerritoryDescription'

		from

			dbo.EmployeeTerritories ET

		INNER JOIN

			dbo.Territories TT

		ON

			ET.TerritoryID = TT.TerritoryID

		WHERE

			ET.EmployeeID = EMP.EmployeeID

		FOR XML PATH('EmployeeTerritort'), ROOT('EmployeeTerritories'), Type

	)

FROM

	dbo.Employees EMP 

--INNER JOIN

--	dbo.Orders ORDO

--ON

--	ORDO.EmployeeID = EMP.EmployeeID

WHERE

	EMP.EmployeeID = 1

FOR XML PATH('Employee'), ROOT('Employees'), TYPE

Query 2:

USE NORTHWND

GO



select 

	 FirstName as '@FirstName1'

	,(

		select ShipName as '@ShipName'

		from

			dbo.Orders ORD

		where

			ORD.EmployeeID = EMP.EmployeeID

		FOR XML PATH('Ship'), ROOT('Ships'), Type

	)

	,(

		select TerritoryDescription as '@TerritoryDescription'

		from

			dbo.EmployeeTerritories ET

		INNER JOIN

			dbo.Territories TT

		ON

			ET.TerritoryID = TT.TerritoryID

		WHERE

			ET.EmployeeID = EMP.EmployeeID

		FOR XML PATH('EmployeeTerritort'), ROOT('EmployeeTerritories'), Type

	)

FROM

	dbo.Employees EMP 

INNER JOIN

	dbo.Orders ORDO

ON

	ORDO.EmployeeID = EMP.EmployeeID

WHERE

	EMP.EmployeeID = 1

FOR XML PATH('Employee'), ROOT('Employees'), TYPE

NOW, the only differnece between these two is INNER JOIN in the outer query. With inner join in the outer query generates NULL result, however, if i comment the outer inner join as in query 1 then it does generate valid xml. WHY ?

I am unable to understand this joining issue and its implication on internal nodes.

ARE there any limitations in TSQL to generate xml using "FOR XML PATH" as compare to other languages ?

Can you explain the reason in detail.

Thanks

Think BIG but Positive, may be GLOBAL better UNIVERSAL.

How to describe the T-SQL WHERE conditions with XML?

Tue, 24 Nov 2009 15:39:51 Z

Hello Gurus:

I have the requirement with XML to describe the conditions in T-SQL Where clause.

i am thinking this XML structure should be like
<Expression Expression1 OPERATOR Expression2 />
The Expression1 as well as Expression2, could be like "ColumnA>45" or still be a structure of nested Expression which means it could "Expression3 operator Expression4".
And the operator here should be "AND" or "OR".

This structure is quite nested binary tree like.

can I describe this pattern with XML?

Derek

XQuery Modify function

Tue, 24 Nov 2009 06:03:12 Z

hi
I have a table in sql server 2005 as Timesheet which has TimesheetNo(varchar) and TimesheetXML(xml).
Corresponding to TimesheetNo, an xml is stored in column TimesheetXML.
The Xml is of type:

TimeCard xmlns="http://ns.hr-xml.org/2006-02-28">
<AdditionalData type="TimeSheetNo">T000001</AdditionalData>
<AdditionalData type="CostCentreDepartment" />
</TimeCard>

I want to update the value 'T000001' to 'LT000001' for all the records in the table.The value 'T000001' is different for each xml. I used modify method but could only replace the entire value but I want to do something like "L" + "T000001" so that it becomes "LT000001".

Need just a little help getting started with query on XML SQL column

Fri, 20 Nov 2009 20:33:05 Z

Hello,

Not sure but the editor to post this problem isn't very friendly...to make sure my acutal question isn't missed..."When I run my xml query, my results come back as "Null""

I have a (what should be) pretty easy one for everyone here, but let me run down where I'm at.

I've created a new table (IP_Test1) with two columns, an ID column and a "Form" column with an XLM type

_______________________________
// table creation

CREATE TABLE [dbo].[IP_Test1](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Form] [xml](DOCUMENT [dbo].[IPFormSchema1]) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

________________________

Next, I've setup an INfopath form to store itself in total into the "Form" column in the table above, as an XML string

_________________________
example of results

ID Form
1 <my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-11-19T18:01:42" ............</my:myFields>

__________________________

Everything to this point works just fine, my problem is, form what I've researched, I should be able to use xquery or something close to that to actually dig out paticular nodes form that xml data, and say buid a view for reporting on.

During the table creation, the guild i followed had me build the namespace in SQL also, so that i believe is working...and i think i'm getting the basics of the query correct, but when i run it, i get "Null" for results...any suggestions?

Here is the query im running, followed by the xml

;WITH XMLNAMESPACES( 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-11-19T18:01:42' AS p ) 




SELECT

form.value('(/p:field1)[1]','nvarchar(30)') AS PartyName

FROM

IP_Test1

_________________
One of the XML records

<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-11-19T18:01:42" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">

  <my:field1>John Q</my:field1>

  <my:field2>myemail@domain.com</my:field2>

  <my:group1>

    <my:group2>

      <my:field3>Toy truck</my:field3>

      <my:field4>20.00</my:field4>

      <my:field5>10</my:field5>

    </my:group2>

    <my:group2>

      <my:field3>Ball</my:field3>

      <my:field4>10.00</my:field4>

      <my:field5>1</my:field5>

    </my:group2>

  </my:group1>

</my:myFields>

Need to insert and update some xml attributes values?

Fri, 20 Nov 2009 09:14:30 Z

Hello,
I have a table 'UnitStatus' with two columns, one is UnitId (bigint) and other is Status (XML).
I just need to insert values in Status (XML) column like this:

<status>
<engine state="1" description="some desc" />
<door state="0" description="some desc" />
<security state="1" description="some desc" />
...
</status>

I know how to insert and update and make above like XML. But the problem is, statuses are more than 30, that is:
<engine...

<door...

<security...

<somestatus...

So, I need to input state of each attribute values + description attribute values. Can I have a list inserted in my store procedure and just split them with ',' and '_' ? Where , (comma) would tell me next element values are followed and _ (underscore) would tell me state is separated with description for particular element tag?

Query XML contents in SQL Server 2000

Thu, 19 Nov 2009 22:51:28 Z

Hi,

I am using the SQL Server 2000 as RDBMS.. I am tasked to develop an application which is supposed to handle user's comments/notes on many different web sites. Each site has its own specific data which uniquely identify the notes/comments entered on it. So, i must store that specific data in my database's table. The problem is that the site specific data is undefined. It could be anything.So, i am not aware about the structure of the specific data. I decided to keep the site specific data into XML. So, I developed a table like below,

NoteID INT
Note VARCHAR(1000)
ApplicationID VARCHAR(10)
UserID VARCHAR(10)
CreateDate DATETIME
ModifiedDate DATETIME
Parameters VARCHAR(1000)

Parameters column actually stores the site specific data in form of XML e.g. '<parameters><profileid>1796</profileid><state>page2</state></parameters>'........

I would pass a parameter to my stored procedure from my application. This parameter enloses the search criteria for the site specific data in form of XML too. For example, I would pass '<parameters><profileid>0001</profileid><state>unknown</state></parameters>' . Rest of the parameters like ApplicationID would pass in a standard way like we do in normal cases. :) So, enough explanation i think, lets come to the point,

How could i query the specific element e.g. profileid, within stored procedure by following the above architecture so that application must be able to render the correct notes/comments on the client? Any help would be highly appreciated.

Thanks.

OPENXML Assistance Please

Thu, 19 Nov 2009 17:05:40 Z

Usin OPENXML I get two rows, but there is two entity_Ratings rows that should return but do not. What am I missing please

query:

-- XML SAMPLE SNP IMPORT IS SOURCE

DECLARE @doc XML;

DECLARE @iDOC INT;

INSERT INTO XML_Text(XML_TEXT)

SELECT * FROM OPENROWSET

(

BULK 'D:\snp1.XML'

, SINGLE_BLOB

) AS X

SELECT @doc = xml_text FROM xml_text

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/GISF_ENTITY/ENTITY/ENTITY_RATING_GROUP/ENTITY_RATINGS',2) -- go to lowest level here

WITH (

[TYPE_OF_RATING] VARCHAR(50) '../ENTITY_RATINGS/TYPE_OF_RATING'

,[SHORT_TERM_RATING] VARCHAR(50) '../ENTITY_RATINGS/SHORT_TERM_RATING'

,[SHORT_TERM_RATING_DATE] VARCHAR(50) '../ENTITY_RATINGS/SHORT_TERM_RATING_DATE'

,[LONG_TERM_RATING_DATE] VARCHAR(50) '../ENTITY_RATINGS/LONG_TERM_RATING_DATE'

,[LONG_TERM_RATING] VARCHAR(50) '../ENTITY_RATINGS/LONG_TERM_RATING'

,[LONG_TERM_OUTLOOOK] VARCHAR(50) '../ENTITY_RATINGS/LONG_TERM_OUTLOOK'

,[PRIOR_CREDIT_RATING] VARCHAR(50) '../ENTITY_RATINGS/PRIOR_CREDIT_RATING'

,[UNSOLICITED_RATING_FLAG] VARCHAR(50) '../ENTITY_RATINGS//UNSOLICITED_RATING_FLAG'

,[ENTITY_ID] varchar(50) '../../ENTITY_ID' -- depending upon hierarchy traverse backwards from lowest level listed above

,[ENTITY_PUBLISHED_NAME] varchar(50) '../../ENTITY_PUBLISHED_NAME'

,[SECTOR] varchar(50) '../../ENTITY_SECTOR/SECTOR'

,[SUB_SECTOR] varchar(50) '../../ENTITY_SECTOR/SUB_SECTOR'

,[UNSOLICITED_RATING_FLAG] varchar(50) 'UNSOLICITED_RATING_FLAG'

,[CREDIT_RATING] varchar(50) 'CREDIT_RATING'

,[RATING_DATE] varchar(50) 'RATING_DATE'

,[COUNTRY_CODE] varchar(50) '../../COUNTRY_CODE'

,[REGION] varchar(50) '../../REGION_GROUP/REGION'

,[STATE_CODE] varchar(20) '../../STATE_CODE'

,[RATING_GROUP_CODE] VARCHAR(50) '../../ENTITY_RATING_GROUP/RATING_GROUP_CODE'

)

EXECUTE sp_xml_removedocument @iDoc;

source:

<?xml version="1.0" encoding="ISO-8859-1" ?>

- <GISF_ENTITY XMLNS="http://www.ratingsxpress.com/ratings/">

- <HEADER>

<FILE_NAME>GISF_ERM_2009_11_02_00_00_00.xml</FILE_NAME>

</HEADER>

- <ENTITY>

<ENTITY_ID>1894</ENTITY_ID>

<ENTITY_PUBLISHED_NAME>SOME NAME</ENTITY_PUBLISHED_NAME>

- <ENTITY_SECTOR>

<SECTOR>GLOBISS</SECTOR>

<SUB_SECTOR>FI</SUB_SECTOR>

</ENTITY_SECTOR>

- <REGION_GROUP>

</REGION_GROUP>

<COUNTRY_CODE>USA</COUNTRY_CODE>

<STATE_CODE>MA</STATE_CODE>

- <ENTITY_RATING_GROUP>

<RATING_GROUP_CODE>ICR</RATING_GROUP_CODE>

- <ENTITY_RATINGS>

<TYPE_OF_RATING>LOCAL CURRENCY</TYPE_OF_RATING>

<UNSOLICITED_RATING_FLAG>N</UNSOLICITED_RATING_FLAG>

<CREDIT_RATING>A+/Stable/A-1</CREDIT_RATING>

<RATING_DATE>20070629 14:22:23</RATING_DATE>

<LONG_TERM_CREDITWATCH />

<LONG_TERM_CREDITWATCH_DATE />

<SHORT_TERM_CREDITWATCH />

<SHORT_TERM_CREDITWATCH_DATE />

<LONG_TERM_OUTLOOK>STABLE</LONG_TERM_OUTLOOK>

<LONG_TERM_OUTLOOK_DATE>20070629 14:22:23</LONG_TERM_OUTLOOK_DATE>

<SHORT_TERM_OUTLOOK />

<SHORT_TERM_OUTLOOK_DATE />

<LONG_TERM_RATING>A+</LONG_TERM_RATING>

<LONG_TERM_RATING_DATE>20070629 14:22:23</LONG_TERM_RATING_DATE>

<SHORT_TERM_RATING>A-1</SHORT_TERM_RATING>

<SHORT_TERM_RATING_DATE>20010803 00:00:00</SHORT_TERM_RATING_DATE>

<PRIOR_CREDIT_RATING>A/Stable/A-1</PRIOR_CREDIT_RATING>

<PRIOR_LONG_TERM_CREDITWATCH />

<PRIOR_LONG_TERM_OUTLOOK>STABLE</PRIOR_LONG_TERM_OUTLOOK>

<PRIOR_SHORT_TERM_CREDITWATCH />

<PRIOR_SHORT_TERM_OUTLOOK />

<PRIOR_LONG_TERM_RATING>A</PRIOR_LONG_TERM_RATING>

<PRIOR_SHORT_TERM_RATING>A-1</PRIOR_SHORT_TERM_RATING>

<ACTION_INDICATOR>A</ACTION_INDICATOR>

</ENTITY_RATINGS>

- <ENTITY_RATINGS>

<TYPE_OF_RATING>FOREIGN CURRENCY</TYPE_OF_RATING>

<UNSOLICITED_RATING_FLAG>N</UNSOLICITED_RATING_FLAG>

<CREDIT_RATING>A+/Stable/A-1</CREDIT_RATING>

<RATING_DATE>20070629 14:22:23</RATING_DATE>

<LONG_TERM_CREDITWATCH />

<LONG_TERM_CREDITWATCH_DATE />

<SHORT_TERM_CREDITWATCH />

<SHORT_TERM_CREDITWATCH_DATE />

<LONG_TERM_OUTLOOK>STABLE</LONG_TERM_OUTLOOK>

<LONG_TERM_OUTLOOK_DATE>20070629 14:22:23</LONG_TERM_OUTLOOK_DATE>

<SHORT_TERM_OUTLOOK />

<SHORT_TERM_OUTLOOK_DATE />

<LONG_TERM_RATING>A+</LONG_TERM_RATING>

<LONG_TERM_RATING_DATE>20070629 14:22:23</LONG_TERM_RATING_DATE>

<SHORT_TERM_RATING>A-1</SHORT_TERM_RATING>

<SHORT_TERM_RATING_DATE>20010803 00:00:00</SHORT_TERM_RATING_DATE>

<PRIOR_CREDIT_RATING>A/Stable/A-1</PRIOR_CREDIT_RATING>

<PRIOR_LONG_TERM_CREDITWATCH />

<PRIOR_LONG_TERM_OUTLOOK>STABLE</PRIOR_LONG_TERM_OUTLOOK>

<PRIOR_SHORT_TERM_CREDITWATCH />

<PRIOR_SHORT_TERM_OUTLOOK />

<PRIOR_LONG_TERM_RATING>A</PRIOR_LONG_TERM_RATING>

<PRIOR_SHORT_TERM_RATING>A-1</PRIOR_SHORT_TERM_RATING>

<ACTION_INDICATOR>A</ACTION_INDICATOR>

</ENTITY_RATINGS>

</ENTITY_RATING_GROUP>

</ENTITY>

</GISF_ENTITY>

Generating XML from table(s) using schema

Thu, 19 Nov 2009 22:06:41 Z

Dear All,

I want to generate XML from table(s) using the given schema. I'm aware about generating XML using for XML clause, but struct on using schema to generate xml. The objective is to come up with a generic module that could generate XML for the given schema.

Can someone help me to get started? Thank you in advance for your help.

With regards
Ganesh

Ganeshkumar

Multiple Xquery in One Stored Procedure

Fri, 20 Nov 2009 10:43:39 Z

Hi,
I am facing an interesting problem. I have a xml file and I need to shred it to two different relational table. I have written the following xquery and it is executing perfectly but if I execute the same query two times then second one is returning empty result set. I believe I need to reset something before executing the second query. Can anybody please help me to figure out the issue? Here is my query:

declare @XmlData xml
set @XmlData='<?xml version="1.0"?>
<ItemSearchResponse xmlns="http://webservices.amazon.com/AWSECommerceService/2005-10-05">
<Items>
    <TotalResults>18579</TotalResults>
    <TotalPages>1858</TotalPages>
    <Item>
      <ASIN>B001MIZNEM</ASIN>
      <DetailPageURL>http://www.amazon.com/Cake-Mania-Game-Download-Pc/dp/B001MIZNEM%3FSubscriptionId%3D0Z6TA0JMJ7NM6RQP0WR2%26tag%3Dws%26linkCode%3Dxm2%26camp%3D2025%26creative%3D165953%26creativeASIN%3DB001MIZNEM</DetailPageURL>
      <SalesRank>343</SalesRank>
      <SmallImage>
        <URL>http://ecx.images-amazon.com/images/I/31JBXb0O1hL._SL75_.jpg</URL>
        <Height Units="pixels">75</Height>
        <Width Units="pixels">75</Width>
      </SmallImage>
      <MediumImage>
        <URL>http://ecx.images-amazon.com/images/I/31JBXb0O1hL._SL160_.jpg</URL>
        <Height Units="pixels">160</Height>
        <Width Units="pixels">160</Width>
      </MediumImage>
      <LargeImage>
        <URL>http://ecx.images-amazon.com/images/I/31JBXb0O1hL.jpg</URL>
        <Height Units="pixels">200</Height>
        <Width Units="pixels">200</Width>
      </LargeImage>
      <ItemAttributes>
        <Binding>Software Download</Binding>
        <Brand>Sandlot Games</Brand>
        <Edition>Standard</Edition>
        <Feature>48 Levels Of Baking Fun.</Feature>
        <Feature>Customize And Upgrade Your Kitchen</Feature>
        <Feature>4 Unique Bakeries To Run</Feature>
        <Feature>Hilarious Seasonal Characters.</Feature>
        <Format>Download</Format>
        <Label>Sandlot Games</Label>
        <Languages>
          <Language>
            <Name>English</Name>
            <Type>Unknown</Type>
          </Language>
        </Languages>
        <Manufacturer>Sandlot Games</Manufacturer>
        <Model>518</Model>
        <Platform>Windows Vista</Platform>
        <Platform>Windows XP</Platform>
        <ProductGroup>Video Games</ProductGroup>
        <Publisher>Sandlot Games</Publisher>
        <ReleaseDate>2006-03-23</ReleaseDate>
        <Studio>Sandlot Games</Studio>
        <Title>Cake Mania [Game Download]</Title>
      </ItemAttributes>
      <EditorialReviews>
        <EditorialReview>
          <Source>Product Description</Source>
          <Content>Help a young baker show big bad business who''s boss in this fast-paced treat! When the retail behemoth "Mega-Mart" moves in, Jill''s beloved family bakery is forced to close. But she''s determined to get the business back on its feet with delicious sweets.   Help Jill open her own bakery, bring in customers, and earn enough money to re-open her grandparents'' bakery! With 48 levels and four different bakeries, Cake Mania is full of mouth-watering fun. Let''s get cookin''!</Content>
        </EditorialReview>
      </EditorialReviews>
    </Item>
</Items>
</ItemSearchResponse>'


    ;WITH XMLNAMESPACES(
    DEFAULT 'http://webservices.amazon.com/AWSECommerceService/2005-10-05'
    )

    select
        x.value('ASIN[1]', 'nvarchar(50)') as [ASIN],
        x.value('DetailPageURL[1]', 'nvarchar(2000)') as DetailPageURL,
        y.value('URL[1]', 'nvarchar(250)') as DefaultImage,
        y.value('Height[1]', 'int') as ImageHeight

    from @xmlData.nodes('/ItemSearchResponse/Items/Item') as v(x)
    outer apply x.nodes('LargeImage') as o(y)

    select
            x.value('ASIN[1]', 'nvarchar(50)') as [ASIN],
            x.value('DetailPageURL[1]', 'nvarchar(2000)') as DetailPageURL,
            y.value('URL[1]', 'nvarchar(250)') as DefaultImage,
            y.value('Height[1]', 'int') as ImageHeight

        from @xmlData.nodes('/ItemSearchResponse/Items/Item') as v(x)
        outer apply x.nodes('LargeImage') as o(y)


And Here is the result:

Shredding XML attribute and element data (openxml)

Thu, 19 Nov 2009 17:41:39 Z

Hi.. this is my first real need to use XML so I'm a bit lost to say the least... I have an application that audits our network and generates an XML file... sample snippet is in the code below... what I need to do is make it into a table...
not sure if I'm doing this right but the OPENXML that I have below almost works except I can't get the ELEMENT values for each row... you can run the code below and see the results for yourself..

Category	Section	SubSection	Entry_Item	Data
Hardware	GENERAL	General	Computer Name	8215JDU
Hardware	GENERAL	General	Last Audited	8215JDU
Hardware	GENERAL	General	Last Booted	8215JDU
Hardware	GENERAL	General	Operating System (O/S)	8215JDU
Hardware	GENERAL	General	O/S Build	8215JDU
Hardware	GENERAL	General	O/S Version	8215JDU
Hardware	GENERAL	General	WindowsDirectory	8215JDU
Hardware	GENERAL	General	Manufacturer	8215JDU
Hardware	GENERAL	General	Model	8215JDU

declare @XMLData varchar(max)
declare @xdoc int

set @XMLData = '<Audit>
	<Category name="Hardware">
		<section name="GENERAL">
			<sub_section name="General">
				<entry name="Computer Name">XP-COMPUTER</entry>
				<entry name="Last Audited">11/17/2009 10:00:02 AM</entry>
				<entry name="Last Booted">10/16/2009 3:24:26 AM</entry>
				<entry name="Operating System (O/S)">Microsoft Windows XP</entry>
				<entry name="O/S Build">2600</entry>
				<entry name="O/S Version ">5.1</entry>
				<entry name="WindowsDirectory">C:\WINDOWS</entry>
				<entry name="Manufacturer">IBM</entry>
				<entry name="Model">8215JDU</entry>
			</sub_section>
		</section>
	</Category>
</Audit>'

exec sp_xml_preparedocument @xdoc output, @XMLData

select *
from openxml (@xdoc,'Audit/Category/section/sub_section/entry',2)
with (Category varchar(50) '../../../@name'
		,Section varchar(50) '../../@name'
		,SubSection varchar(50) '../@name'
		,Entry_Item varchar(50) '@name'
		,Data varchar(50) '../entry[@name="Model"]')

exec sp_xml_removedocument @xdoc

How to INSERT and UPDATE in single xquery?

Thu, 19 Nov 2009 08:43:53 Z

Hi,
I am in situation where I would like to have a XML like below:

<markers>

<line>

</line>

</status>
</markers>

What I have done so far is, I can insert new <point> as last element within <line> element. So far so good!

Now, as I have mentioned in above example. I need to have <status> element (its inner nodes) be modified each time with some input parameters. So, how can I make an update/modify while inserting?

Here is my current xquery:

ALTER PROCEDURE dbo.StoredProcedure3 @column1var [int], @column2var [varchar](500) AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT column2 FROM Table1 WHERE column1 = @column1var) BEGIN UPDATE [Table1] SET column2.modify(' insert <point attr="saad" lat="{sql:variable("@column2var")}" lng="{sql:variable("@column2var")}" /> as last into (/markers/line)[1] ') WHERE column1 = @column1var END ELSE BEGIN INSERT INTO Table1 (column1, column2) VALUES (@column1var, '<markers> <line> <point lat="{sql:variable("@column2var")}" lng="{sql:variable("@column2var")}" /> </line> </markers>') END END

Getting the URL out of a XML Structure

Thu, 19 Nov 2009 04:51:23 Z

I have a query like this and All I want is the Audio elements that contact the URL PATH to the Audio. When I try to retreive it, it gives me just the titkle for each.




DECLARE @sql nvarchar(4000) 
SELECT @sql = '  <root>  <PresentationTitle>Revaluation of the G-codes - Update on the Physician Payment Survey </PresentationTitle>  <Description><p>Description - Revaluation of the G-codes - Update on the Physician Payment Survey</p> </Description>  <Speakers>500</Speakers><Speakers>504</Speakers><Speakers>1072</Speakers>  <AudioFile><a title="Revaluation of the G-codes - Update on the Physician Payment Survey - Audio" href="/WorkArea/linkit.aspx?LinkIdentifier=id&amp;ItemID=242">Revaluation of the G-codes - Update on the Physician Payment Survey - Audio</a></AudioFile>  <AudioFile><a title="Audio2 - Update on the Physician Payment Survey - Audio" href="/WorkArea/linkit.aspx?LinkIdentifier=id&amp;ItemID=242">Audio2 - Update on the Physician Payment Survey - Audio</a></AudioFile>  <AudioFile><a title="Audio3 - Update on the Physician Payment Survey - Audio" href="/WorkArea/linkit.aspx?LinkIdentifier=id&amp;ItemID=242">Audio3 - Update on the Physician Payment Survey - Audio</a></AudioFile>  <PresentationSlides><a title="Presentation1 - Update on the Physician Payment Survey - Handout" href="/WorkArea/linkit.aspx?LinkIdentifier=id&amp;ItemID=243">Revaluation of the G-codes - Update on the Physician Payment Survey - Handout</a></PresentationSlides>  <PresentationSlides><a title="Presentation2 - Update on the Physician Payment Survey - Handout" href="/WorkArea/linkit.aspx?LinkIdentifier=id&amp;ItemID=243">Revaluation of the G-codes - Update on the Physician Payment Survey - Handout</a></PresentationSlides>  <PresentationSlides><a title="Presentation3 - Update on the Physician Payment Survey - Handout" href="/WorkArea/linkit.aspx?LinkIdentifier=id&amp;ItemID=243">Revaluation of the G-codes - Update on the Physician Payment Survey - Handout</a></PresentationSlides>  </root>' 
FROM content 
WHERE content_id = 244 
declare @x as xml 
set @x= @sql 
select x.i.value('.','varchar(4000)') as Audio 
from @x.nodes('//root/AudioFile')x(i)

SQL Tree to XML

Thu, 05 Nov 2009 18:44:53 Z

Thanks for the help, I am fairly new to XML.

I want to create a solid query that will turn this table structure into an xml document with hardcoding the ammount of levels this could go.

DECLARE @menugroups TABLE (
	groupid INT,
	parentid INT,
	grouptext VARCHAR(30)
)
DECLARE @menuitems TABLE(
	menuid INT,
	groupid INT,
	menutext VARCHAR(50),
	url VARCHAR(50)
)

INSERT INTO @menugroups (groupid,parentid,grouptext) VALUES ( 	/* groupid - INT */ 1,/* parentid - INT */ 0,/* grouptext - VARCHAR(30) */ 'Customer Service' ) 
INSERT INTO @menugroups (groupid,parentid,grouptext) VALUES ( 	/* groupid - INT */ 2,/* parentid - INT */ 1,/* grouptext - VARCHAR(30) */ 'Reports' ) 
INSERT INTO @menugroups (groupid,parentid,grouptext) VALUES ( 	/* groupid - INT */ 3,/* parentid - INT */ 1,/* grouptext - VARCHAR(30) */ 'Operations' )

INSERT INTO @menuitems (menuid,groupid,menutext,url) VALUES (	/* menuid - INT */ 1,/* groupid - INT */ 2,/* menutext - VARCHAR(50) */ 'Report1',/* url - VARCHAR(50) */ 'report1.com' ) 
INSERT INTO @menuitems (menuid,groupid,menutext,url) VALUES (	/* menuid - INT */ 2,/* groupid - INT */ 2,/* menutext - VARCHAR(50) */ 'Report2',/* url - VARCHAR(50) */ 'report2.com' ) 

INSERT INTO @menuitems (menuid,groupid,menutext,url) VALUES (	/* menuid - INT */ 3,/* groupid - INT */ 3,/* menutext - VARCHAR(50) */ 'Form1',/* url - VARCHAR(50) */ 'report2.com' ) 
INSERT INTO @menuitems (menuid,groupid,menutext,url) VALUES (	/* menuid - INT */ 4,/* groupid - INT */ 3,/* menutext - VARCHAR(50) */ 'Form2',/* url - VARCHAR(50) */ 'report2.com' ) 
/*
Customer Service
	Reports
		Report1
		Report2
		
	Operations
		Form1
		Form2
*/

convert sql selects into xml format

Tue, 17 Jul 2007 09:43:16 Z

hai all,

I need some help and is very urget. I wan to convert the output of the sql query into xml format but i don hv any idea on how to do it. I'm using vb.net for this application.

Hope your guys out there can help me on this.

Regards,

KYY

Converting a text datatype to xml datatype

Tue, 17 Nov 2009 07:35:28 Z

While trying to insert a text datatype into a xml i am getting the following error.

XML parsing: line 1, character 39, unable to switch the encoding

Following is the code

CREATE

TABLE #T1 (Col2 xml)
INSERT INTO #T1 values ('<?xml version="1.0" encoding="utf-16"?><doc></doc>')

Please help. Thanks in advance.

SQLXML Bulk Load error: "Schema: the parent/child table relationship on 'call' does not match error 80004005 Source: Schema Mapping

Wed, 14 Oct 2009 17:18:18 Z

Hi All,
I have attempted to create a schema file for my xml data but am receiving the above error message. I have not been able to pinpoint the problem.

Here is my schema:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xs:annotation>
   <xs:appinfo>

   

      <sql:relationship name="CallEventsCallnum"
         parent="Calls"
      parent-key="callnum"
         child="Events"
      child-key="callnum" />

<sql:relationship name="CallEventsDate"
         parent="Calls"
    parent-key="date"
         child="Events"
    child-key="date" />

   
    <sql:relationship name="EventsExtDate"
         parent="Events"
    parent-key="date"
         child="EventExtensions"
    child-key="date" />

      <sql:relationship name="EventsExtCallnum"
         parent="Events"
    parent-key="callnum"
         child="EventExtensions"
    child-key="callnum" />

<sql:relationship name="EventsExtEventnum"
         parent="Events"
    parent-key="eventnum"
         child="EventExtensions"
    child-key="eventnum" />

   
    <sql:relationship name="EventsAgentsDate"
         parent="Events"
    parent-key="date"
         child="EventAgents"
    child-key="date" />

      <sql:relationship name="EventsAgentsCallnum"
         parent="Events"
    parent-key="callnum"
         child="EventAgents"
    child-key="callnum" />

<sql:relationship name="EventsAgentsEventnum"
         parent="Events"
    parent-key="eventnum"
         child="EventAgents"
    child-key="eventnum" />

   
    <sql:relationship name="EventsQueuesDate"
         parent="Events"
    parent-key="date"
         child="EventQueues"
    child-key="date" />

      <sql:relationship name="EventsQueuesCallnum"
         parent="Events"
    parent-key="callnum"
         child="EventQueues"
    child-key="callnum" />

<sql:relationship name="EventsQueuesEventnum"
         parent="Events"
    parent-key="eventnum"
         child="EventQueues"
    child-key="eventnum" />

   
    <sql:relationship name="EventsTrunksDate"
         parent="Events"
    parent-key="date"
         child="EventTrunks"
    child-key="date" />

      <sql:relationship name="EventsTrunksCallnum"
         parent="Events"
    parent-key="callnum"
         child="EventTrunks"
    child-key="callnum" />

      <sql:relationship name="EventsTrunksEventnum"
         parent="Events"
    parent-key="eventnum"
         child="EventTrunks"
    child-key="eventnum" />
   </xs:appinfo>
</xs:annotation>

   <xs:element name="call_filter_results" >
    <xs:complexType>
      <xs:sequence>
        <xs:element name="calls" sql:is-constant="1" >
          <xs:complexType>
            <xs:sequence>
              <xs:element name="call" sql:relation="Call" sql:relationship="CallEventsCallnum CallEventsDate" >
                 <xs:complexType>
                  <xs:sequence>
                    <xs:element name="events" >
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element   name="event" sql:relation="Events"
              sql:relationship="CallEventsCallnum CallEventsDate">
                            <xs:complexType>
                              <xs:sequence >
                                <xs:choice >
                                  <xs:element   name="ext" sql:relation="EventExtensions"
                                 sql:relationship="EventsExtDate EventsExtCallnum EventsExtEventnum">

                                    <xs:complexType>
                                      <xs:attribute name="key" type="xs:string" use="optional" />
                                      <xs:attribute name="id" type="xs:unsignedShort" use="optional" />
                                      <xs:attribute name="name" type="xs:string" use="optional" />
                                      <xs:attribute name="nick" type="xs:string" use="optional" />
                                      <xs:attribute name="vport" type="xs:unsignedByte" use="optional" />
                                      <xs:attribute name="autoattend" type="xs:unsignedByte" use="optional" />
                                      <xs:attribute name="qcc" type="xs:unsignedByte" use="optional" />
                                    </xs:complexType>
                                  </xs:element>
                                  <xs:element name="trnk" sql:relation="EventTrunks"
                                sql:relationship="EventsTrunksDate EventsTrunksCallnum EventsTrunksEventnum ">
                                    <xs:complexType>
                                      <xs:attribute name="key" type="xs:string" use="optional" />
                                      <xs:attribute name="id" type="xs:string" use="optional" />
                                      <xs:attribute name="name" type="xs:string" use="optional" />
                                      <xs:attribute name="nick" type="xs:string" use="optional" />
                                    </xs:complexType>
                                  </xs:element>
                                  <xs:element name="queue" sql:relation="EventQueues"
                                sql:relationship="EventsQueuesDate EventsQueuesCallnum EventsQueuesEventnum ">
                                    <xs:complexType>
                                      <xs:attribute name="key" type="xs:string" use="optional" />
                                      <xs:attribute name="id" type="xs:unsignedShort" use="optional" />
                                      <xs:attribute name="name" type="xs:string" use="optional" />
                                      <xs:attribute name="nick" type="xs:string" use="optional" />
                                    </xs:complexType>
                                  </xs:element>
                                  <xs:element   name="agent" sql:relation="EventAgents"
                               sql:relationship="EventsAgentsDate EventsAgentsCallnum EventsAgentsEventnum ">
                                    <xs:complexType>
                                      <xs:attribute name="key" type="xs:string" use="optional" />
                                      <xs:attribute name="id" type="xs:unsignedShort" use="optional" />
                                      <xs:attribute name="name" type="xs:string" use="optional" />
                                      <xs:attribute name="nick" type="xs:string" use="optional" />
                                    </xs:complexType>
                                  </xs:element>
                                </xs:choice>
                              </xs:sequence>
                              <xs:attribute name="starttime" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="callkey" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="endtime" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="type" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="ani" type="xs:string" use="optional" />
                              <xs:attribute name="dnis" type="xs:string" use="optional" />
                              <xs:attribute name="digits" type="xs:string" use="optional" />
                              <xs:attribute name="cause" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="montd" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="rqud" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="intf" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="overflowed" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="ani_prefix" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="ani_areacode" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="digits_prefix" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="digits_areacode" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="dnis_prefix" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="dnis_areacode" type="xs:unsignedShort" use="optional" />
                            </xs:complexType>
                          </xs:element>
                         </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                  <xs:attribute name="id" type="xs:string" use="optional" />
                  <xs:attribute name="callkey" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="date" type="xs:date" use="optional" />
                  <xs:attribute name="acd" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="starttime" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="endtime" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="type" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="result" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="from" type="xs:string" use="optional" />
                  <xs:attribute name="to" type="xs:string" use="optional" />
                  <xs:attribute name="dnis" type="xs:string" use="optional" />
                  <xs:attribute name="ansby" type="xs:string" use="optional" />
                  <xs:attribute name="requeues" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="intf" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="overflowed" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="transfd" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="held" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="confd" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="timetoabn" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="timetoans" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="tottalktm" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="totconftm" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="totholdtm" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="ucid" type="xs:unsignedLong" use="optional" />
                 </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
       </xs:element>
      </xs:sequence>
      <xs:attribute name="ver" type="xs:decimal" use="optional" />
    </xs:complexType>
</xs:element>
</xs:schema>

Any help would be greatly appreciated.

Thank you in advance.

issues using xQuery on SQL server 2005

Fri, 13 Nov 2009 14:14:45 Z

Hi

I am trying to run a simple xQuery

select experimentchemical.query('/root/ChemicalName') from students

(can be seen in http://www.sql-server-performance.com/articles/dev/xml_data_2005_p1.aspx)

I have used this on my DB replacing the DB names and XML path as appropriate and ensurig I am querying the correct DB.

However, I get the following error:

Msg 4121, Level 16, State 1, Line 1
Cannot find either column " columnName " or the user-defined function or aggregate "columnName.query", or the name is ambiguous.

Is there something obvious i'm missing? I can query the column in a normal query so i know there are no mispellings. This is the first time I have tried to query XML.

Any advice would be most appreciated (as its really annoying me now)

Thanks

XPath Query

Fri, 13 Nov 2009 00:54:34 Z

SELECT UploadFileID, YEAR, Quarter, CompanyID, FileType, Filename, Description, Success, Addby, AddDttm,
       FileContent.query('LCFS-Report/Fuel/FuelPathwayCode') as FuelPathwayCode,
       FileContent.query('LCFS-Report/Fuel/PhysicalPathwayCode') as PhysicalPathwayCode
FROM LC_UploadXML
WHERE UploadFileID = 8

This is return XML fragment for FuelPathwayCode.
<FuelPathwayCode>CARBOB001</FuelPathwayCode>
<FuelPathwayCode>CARBOB002-HIGH</FuelPathwayCode>
<FuelPathwayCode>CARBOB002-INC</FuelPathwayCode>
<FuelPathwayCode>Electricity001_30</FuelPathwayCode>
<FuelPathwayCode>Electricity002_27</FuelPathwayCode>

How do I retrieve the FuelPathwayCode values instead?

I want the result set to be as follows:

Year Quarter FuelPathWayCode PhysicalPathwayCode
2010 4           CARBOB001          Rail
2010 4           CARBOB002          Rail
2010 4           CARBOB001          Rail
2010 4           Electricity001_30    Rail
2010 4           Electricity002_27 Rail

SQL Server 2008

How would I use .modify() correctly here?

Sun, 08 Nov 2009 16:19:59 Z

My ScriptTable_fn returns script command as an xml element. The problem occurs on the .modify(). I’m not sure how to use it properly. I’ve looked around and could not find a solution. Any help would be appreciated a lot. Thanks

CREATE TRIGGER [tableTrg]

ON DATABASE

FOR CREATE_TABLE, ALTER_TABLE

DECLARE @Body XML

DECLARE @tableName nvarchar(100)

DECLARE @tableTsqlCommand XML

DECLARE @script XML

SET @Body = eventdata() -- get all other attributes...login, datetime, etc.

SET @tableName = @Body.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') -- get name of table to be scripted

SET @script = (select dbo.ScriptTable_fn(@tableName))

SET @Body.modify('replace value of (/EVENT_INSTANCE/tsqlcommand) [1] with "(@script)"')

EXEC dbo.sendmsg_sp @Body

A View Over a XML Data Type Column

Thu, 12 Nov 2009 00:37:06 Z

I have a table that contains a column of XML Datatype (column name FileContent). I want to create a view that queries the contents of the XML datatype column so that I no longer have FileContent column but two additional columns called FuelPathwayCode and PhysicalPathwayCode from the underlying XML document. Is this possible with SQL Server 2008? And, if so, how? Thanks.

CREATE VIEW vw_LC_UploadXML
AS Select
UploadFileID,
Year,
Quarter,
CompanyID,
FileType,
FileContent, <<<XML Data type column
FileName,
Description,
Success,
AddBy,
AddDttm
FROM LC_UploadXML

Example using OpenXML with Parent Child Relationship and Associated Lookup Tables

Wed, 11 Nov 2009 06:50:59 Z

I have an XML document that is loaded into a column of a table of the XML data type. Is there any good examples showing how to insert using a store procedure and OpenXML into a parent tables with primary key and also into a child table with it's associated foreign key? Both the Parent and Child have relationships to lookup tables. The XML documents does not contain the foreign keys but the look up values instead. The look up values need to found in order to get the key from the lookup table to be inserted into either the Parent or Child tables...

Besides OPENXML and a Store Procedure, is there any other Microsoft tool (SSIS?) or .NET class that can be used to map a XML document to relational tables to insert the contents of the XML file?

XML data type vs varchar(MAX)

Thu, 17 Jan 2008 14:18:00 Z

Hello

Suppose that I have to storage string content which represents an XML document but it is only manipulated in the client side. Which of the mentioned data types is recomended for this scenario?

Thanks a lot.

CLR Garbage disposal

Wed, 11 Nov 2009 17:02:26 Z

Hopefully this is sufficiently on topic here. I am modifying some legacy code, attempting to improve the garbage collection in a CLR function. Unfortunately, I have very little experience with the objects involved.

the code does an xsl transform. It creates a memory stream when it does the transform, and loads the memory stream into a new SqlXml object and outputs it. I want to explicitly dispose the memory stream, but when I do, I get the error:

'Invalid attempt to call Read when the stream is closed.'

here is my code in its current state, although I've tried several variations:

public static SqlXml SqlXslTransform(SqlXml XmlData, SqlXml XslData)
{
// Load XSL transformation
System.Xml.Xsl.XslCompiledTransform xform = new System.Xml.Xsl.XslCompiledTransform();
xform.Load(XslData.CreateReader());
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
xform.Transform(XmlData.CreateReader(), null, ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
// Return the transformed value
SqlXml retSqlXml = new SqlXml(ms);
return (retSqlXml);

I've tried to figure out how I can declare the retSqlXml early, rather than create it inside the using{} block, but I don't see any sort of load() method. Is there a way to dispose of the memory stream and still output the SqlXml object, or do I have to leave the stream open? Is it creating a memory leak if I do? We are having memory pressure issues on the server caused by this function.

Any help appreciated.

How to do this as a XML output?

Sun, 08 Nov 2009 18:58:27 Z

I have a table that logs errors for multiple SSIS packages and some attributes. The table structure is as follows:

Table Validation_Errors
errorid (int) PK,
packagename (varchar),
period (int),
charges (money),
collections (money)

Some sample Data:
1, Staging2Production, 10910, 12, 0
2, Staging2Production, 10910, 13, 23
3, Staging2Production, 10911, 43, 18
4, Extract2Staging, 10910, 11, 21
5, Extract2Staging_Group, 10906, 11.5, 121

I would like to write a sql/xml query that outputs XML as follows:

<root>
<Staging2Production>
    <errorid = "1">
      <period= "10910">
      <charges = "12">
      <collections = "0">
    </errorid>
    <errorid = "2">
      <period= "10910">
      <charges = "13">
      <collections = "23">
    </errorid>
    <errorid = "3">
      <period= "10911">
      <charges = "43">
      <collections = "18">
    </errorid>
</Staging2Production>
<Extract2Staging>
    <errorid = "4">
      <period= "10910">
      <charges = "11">
      <collections = "21">
    </errorid>
</Extract2Staging>
<Extract2Staging_Group>
    <errorid = "5">
      <period= "10906">
      <charges = "11.5">
      <collections = "121">
    </errorid>
</Extract2Staging_Group>
</root>

Your help is greatly appreciated.

Thank you.

-Aashish

How to Create a Table from XML Schema File (.xsd) in SSIS 2005

Thu, 05 Jun 2008 12:25:39 Z

Hi,
I have a .xsd schema file from an Informix table. I want to create the same table in the SQL Server 2005 using SSIS 2005. Is there any work arround that i can do this from a file?
OR
If could do this using any other SQL Server 2005 file Objects.

Immidiate Help would be appriciated.
Thanks!

Error: "Index was outside the bounds of the array." in Sql Mng Studio

Thu, 16 Oct 2008 17:41:05 Z

Sql Server 2008 Standard:

To isolate the problem I'm having, I've created a simple Dbs with just one
table, having an "int" field and an "xml" field.

The xml field is assigned to a SchemaCollection, so it is typed.
Updating the xml field with XML data works 99.99 of the times, but I have 2 specific XML fragments that systematically give me problems. Updating the xml field in the table with this data works fine without errors, however when I do a SELECT * from the table in the Management Studio, I get the error:

"An error occurred while executing batch. Error message is: Index was outside the bounds of the array."

Changing "SELECT xmlField FROM tab" to "SELECT CAST(xmlField AS nvarchar(MAX)) FROM tab" appears to give me the correct xml string, so the data is there!

Querying the data from .Net code gives the same error. Here's part of the stack trace:

[IndexOutOfRangeException: Index was outside the bounds of the array.]
System.Xml.XmlSqlBinaryReader.GetXsdKatmaiTokenLength(BinXmlToken token) +1207761
System.Xml.XmlSqlBinaryReader.ScanOverAnyValue(BinXmlToken token, Boolean attr, Boolean checkChars) +524
System.Xml.XmlSqlBinaryReader.ScanOverValue(BinXmlToken token, Boolean attr, Boolean checkChars) +159
System.Xml.XmlSqlBinaryReader.ImplReadData(BinXmlToken tokenType) +119
System.Xml.XmlSqlBinaryReader.ReadDoc() +589
System.Xml.XmlSqlBinaryReader.Read() +201

I get this exact problem on different machines: both the production Server 2003 and my Vista development PC.

The funny thing is that if I remove a few things from the XML, and re-update, the error goes away, but there's no structure in what I have to remove. As if the XML content is triggering some rare bug in the Sql server code?

I've been successfully using XML fields in SQL2005 for 3 years now, and have recently upgraded to SQL2008.

This has me completely puzzled.
Is there any known problem that might be related or anyone has a clue how to fix this? Would be warmly appreciated.

Thanks!

Where does a value occur in an XML snippet?

Fri, 06 Nov 2009 15:51:20 Z

Hi,
I am using using .nodes(),.query() & .value() to return values from inside of my XML documents - this is working fine.

What I would really like to do is know from WHERE in the XML document the value occurred. For example, take the following (very silly) query:

declare @x xml = '	<root>
						<node1>node1</node1>
						<node2>
							<node4>node4</node4>
						</node2>
						<node3>node3</node3>
					</root>';
select	nd.value('.','nvarchar(max)')
from	@x.nodes('//.') X(nd)

Is there a way of finding out exactly where (defined by an XPath) in the original XML document the returned values come from?

Essentially I want to say to my user, "I have found these values in your XML document that might warrant your attention, here is where you can go to find them!"

Hope that makes sense!

thanks
Jamie

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

How to convert a XML column content to a relationnal form

Thu, 05 Nov 2009 10:55:12 Z

Hi,

I wonder if it's possible (with a sql & xpath request) to "convert" a set of lines having in one nvarchar(max) column, some XML like this :

<ArrayOfBedControlResult>

    <BedControlResult>

        <Location>S07Robot</Location>
        <Name>Callage sur cible X1</Name>
        <MeasureData>0</MeasureData>
        <Status>1</Status>
        <IsCorrected>false</IsCorrected>
        <Notes>Calib_X1 : OK</Notes>

    </BedControlResult>

    <BedControlResult>

        <Location>S07Robot</Location>
        <Name>Callage sur cible X2</Name>
        <MeasureData>0</MeasureData>
        <Status>1</Status>
        <IsCorrected>false</IsCorrected>
        <Notes>Calib_X2 : OK</Notes>

    </BedControlResult>
    ...
    ...
</ArrayOfBedControlResult>

to a relationnal form like this :

Location      Name                      MeasureData        Status        IsCorrected        Notes
---------------------------------------------------------------------------------------------------------------
S07Robot    Callage sur cible X1    0               1               false                  Calib_X1 : OK
S07Robot    Callage sur cible X2    0               1           false                  Calib_X2 : OK
...
...

My xpath knowledge is near 0 and this problem seem to me too hard for a first contact ...
What I ve done for now is this to test with one field (Location), but nothing is returned :

SELECT Convert(XML,cp.Data).query('for $Location in /root/ArrayOfBedControlResult/BedControlResult/Location return $Location') as Result FROM Core_BaseCustomProperty cp

Does anybody have a idea how to do this ?

Thanks a lot for any help !

Yann.