Good evening
I want to open a local XML file from my SQL script.
#1. If you are in some dev't environment such as .Net or script, you can
open the file and pass it into the SQL script. But I want that within SQL
script.
#2. You can write a CLR function to open it. I did. However I feel like pay
too much for such a trivial job.
#3. I found a way to retrieve a file as base64 string. e.g.,
declare @.b varbinary(max)
set @.b=(select * from openrowset(bulk N'c:\x.xml', single_blob) as b)
That looks like decent base64 binary text which can be converted into XML
stream easily, if you are in .Net. But how do you do that within SQL script?
Or is there another way #4, #5, ... I have not imagined?
#2 UDF
public static SqlXml getdoc(string xmlPath)
{
XmlReader xr = XmlReader.Create(xmlPath);
SqlXml retSqlXml = new SqlXml(xr);
xr.Close();
return (retSqlXml);
}
Pohwan Han. Seoul. Have a nice day.declare @.b xml
set @.b=(select * from openrowset(bulk N'c:\x.xml', single_blob) as b)
SELECT @.b
VARBINARY can be implicitly converted to xml, assuming the stream actually
is xml.
Dan
> declare @.b varbinary(max)
> set @.b=(select * from openrowset(bulk N'c:\x.xml', single_blob) as b)|||Thanks Dan.
It worked.
Pohwan Han. Seoul. Have a nice day.
"Dan Sullivan" <danATpluralsight.com> wrote in message
news:964a9ae6140488c868bbaa3f4180@.news.microsoft.com...
> declare @.b xml
> set @.b=(select * from openrowset(bulk N'c:\x.xml', single_blob) as b)
> SELECT @.b
> VARBINARY can be implicitly converted to xml, assuming the stream actually
> is xml.
> Dan
>
>sql
Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
OPEN XML vs. SQLXMLBulkLoad
Which approach is a faster, better solution to process XML data? I get XML
data from an external web service. To ballpark the general size, an average
data file would be approximately 64K when saved in UTF-8.
A) TEXT parameter/sp_xml_preparedocument/OPENXML
- Passing raw XML as a TEXT parameter into a stored procedure
- sp_xml_preparedocument to parse the XML into an XML handle.
- Process the data using T-SQL with OPENXML
B) SQLXMLBULKLOAD/staging table/stored procedure
- Save the XML to a flat file
- Use the COM object SQLXMLBULKLOAD to bulk load the XML into a staging
table.
- Call a stored procedure which processes the data in the staging table
using T-SQL.
Any insight into this would be greatly appreciated.I prefer option B. This allows me to bulkload (i.e. minimal log) and then
use tsql to do whatever data insert in set. Also, this allows me to hand of
some of the workload to the client (i.e. workstation that does xmlbulkload).
-oj
"AsaMonsey" <AsaMonsey@.discussions.microsoft.com> wrote in message
news:A44A0EB0-2C15-4477-8FD3-93C7379CF9E0@.microsoft.com...
> Which approach is a faster, better solution to process XML data? I get XML
> data from an external web service. To ballpark the general size, an
> average
> data file would be approximately 64K when saved in UTF-8.
> A) TEXT parameter/sp_xml_preparedocument/OPENXML
> - Passing raw XML as a TEXT parameter into a stored procedure
> - sp_xml_preparedocument to parse the XML into an XML handle.
> - Process the data using T-SQL with OPENXML
> B) SQLXMLBULKLOAD/staging table/stored procedure
> - Save the XML to a flat file
> - Use the COM object SQLXMLBULKLOAD to bulk load the XML into a staging
> table.
> - Call a stored procedure which processes the data in the staging table
> using T-SQL.
>
> Any insight into this would be greatly appreciated.|||Thanks oj,
Our performance benchmarking across 1000 files indicates that the bulk load
is about 60% faster.
I was wondering if someone could explain the technical reasons why option B
is faster.
"oj" wrote:
> I prefer option B. This allows me to bulkload (i.e. minimal log) and then
> use tsql to do whatever data insert in set. Also, this allows me to hand o
f
> some of the workload to the client (i.e. workstation that does xmlbulkload
).
> --
> -oj
>
> "AsaMonsey" <AsaMonsey@.discussions.microsoft.com> wrote in message
> news:A44A0EB0-2C15-4477-8FD3-93C7379CF9E0@.microsoft.com...
>
>|||This article should help explain some:
[url]http://msdn.microsoft.com/library/en-us/dnsql90/html/exchsqlxml.asp?frame=true[/ur
l]
<quote>
SQLXML Bulkload enables the loading of input XML into the relational
backend. Internally, it uses the SQL Server bcp process, and is the best
mechanism to efficiently upload large input XML into the server. It is
implemented as a COM object, and it uses SQLOLEDB providers.
</quote>
-oj
"AsaMonsey" <AsaMonsey@.discussions.microsoft.com> wrote in message
news:F989E8E7-9B24-482F-AC85-7D848D028D08@.microsoft.com...
> Thanks oj,
> Our performance benchmarking across 1000 files indicates that the bulk
> load
> is about 60% faster.
> I was wondering if someone could explain the technical reasons why option
> B
> is faster.
> "oj" wrote:
>
data from an external web service. To ballpark the general size, an average
data file would be approximately 64K when saved in UTF-8.
A) TEXT parameter/sp_xml_preparedocument/OPENXML
- Passing raw XML as a TEXT parameter into a stored procedure
- sp_xml_preparedocument to parse the XML into an XML handle.
- Process the data using T-SQL with OPENXML
B) SQLXMLBULKLOAD/staging table/stored procedure
- Save the XML to a flat file
- Use the COM object SQLXMLBULKLOAD to bulk load the XML into a staging
table.
- Call a stored procedure which processes the data in the staging table
using T-SQL.
Any insight into this would be greatly appreciated.I prefer option B. This allows me to bulkload (i.e. minimal log) and then
use tsql to do whatever data insert in set. Also, this allows me to hand of
some of the workload to the client (i.e. workstation that does xmlbulkload).
-oj
"AsaMonsey" <AsaMonsey@.discussions.microsoft.com> wrote in message
news:A44A0EB0-2C15-4477-8FD3-93C7379CF9E0@.microsoft.com...
> Which approach is a faster, better solution to process XML data? I get XML
> data from an external web service. To ballpark the general size, an
> average
> data file would be approximately 64K when saved in UTF-8.
> A) TEXT parameter/sp_xml_preparedocument/OPENXML
> - Passing raw XML as a TEXT parameter into a stored procedure
> - sp_xml_preparedocument to parse the XML into an XML handle.
> - Process the data using T-SQL with OPENXML
> B) SQLXMLBULKLOAD/staging table/stored procedure
> - Save the XML to a flat file
> - Use the COM object SQLXMLBULKLOAD to bulk load the XML into a staging
> table.
> - Call a stored procedure which processes the data in the staging table
> using T-SQL.
>
> Any insight into this would be greatly appreciated.|||Thanks oj,
Our performance benchmarking across 1000 files indicates that the bulk load
is about 60% faster.
I was wondering if someone could explain the technical reasons why option B
is faster.
"oj" wrote:
> I prefer option B. This allows me to bulkload (i.e. minimal log) and then
> use tsql to do whatever data insert in set. Also, this allows me to hand o
f
> some of the workload to the client (i.e. workstation that does xmlbulkload
).
> --
> -oj
>
> "AsaMonsey" <AsaMonsey@.discussions.microsoft.com> wrote in message
> news:A44A0EB0-2C15-4477-8FD3-93C7379CF9E0@.microsoft.com...
>
>|||This article should help explain some:
[url]http://msdn.microsoft.com/library/en-us/dnsql90/html/exchsqlxml.asp?frame=true[/ur
l]
<quote>
SQLXML Bulkload enables the loading of input XML into the relational
backend. Internally, it uses the SQL Server bcp process, and is the best
mechanism to efficiently upload large input XML into the server. It is
implemented as a COM object, and it uses SQLOLEDB providers.
</quote>
-oj
"AsaMonsey" <AsaMonsey@.discussions.microsoft.com> wrote in message
news:F989E8E7-9B24-482F-AC85-7D848D028D08@.microsoft.com...
> Thanks oj,
> Our performance benchmarking across 1000 files indicates that the bulk
> load
> is about 60% faster.
> I was wondering if someone could explain the technical reasons why option
> B
> is faster.
> "oj" wrote:
>
OPen xml rowpattern probs!
Hi All,
I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.
Definitely doing something wrong here, please advise!
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
SELECT *
FROM
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @.docHandleDon't worry all sortedsql
I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.
Definitely doing something wrong here, please advise!
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
SELECT *
FROM
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @.docHandleDon't worry all sortedsql
OPEN XML QUESTION
DECLARE @.MetaTagXML XML
declare @.XmlDocumentHandlerINT
DECLARE@.QuestionName TABLE(
IdINT IDENTITY(1,1),
[metatag][Varchar](100))
SELECT @.MetaTagXML ='<Report ReportGUID
="CBFF8200-3B52-4F24-9D48-AC5458720B41">
<MetaTags>
<MetaTag>Awareness</MetaTag>
<MetaTag>advertising</MetaTag>
</MetaTags>
<Variables>
<Variable NodeID="7700">
<MetaTags>
<MetaTag>Evaluation</MetaTag>
<MetaTag>account management/primary source experience</MetaTag>
</MetaTags>
</Variable>
<Variable NodeID="7701">
<MetaTags>
<MetaTag>quality</MetaTag>
<MetaTag>quality of account management</MetaTag>
</MetaTags>
</Variable>
</Variables>
</Report>'
EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
SELECT*
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags',2)
WITH (
MetaTag varchar(100),
NodeID varchar(100) '../@.NodeID'
)
EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
i am getting the resultset like this:
Evaluation7700
quality7701
I expect like this
Evaluation7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
account management/primary source experience 7700
CBFF8200-3B52-4F24-9D48-AC5458720B41
quality7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
1. how to get all records
2. how to reference reportguid
Thanks in advance
Deva
You want one row per MetaTag element, so your row pattern in OpenXML needs
to select that element.
And you need to add a row for the ReportGUID:
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags/MetaTag',2)
WITH (
MetaTag varchar(100) '.',
NodeID varchar(100) '../../@.NodeID',
ReportID varchar(100) '../../../../@.ReportGUID'
)
Also note that since you are using OpenXML, using the XML datatype is not
necessarily beneficial, since sp_xml_preparedocument will serialize the XML
and reparse it. So using nvarchar(max) may be the better approach to pass
the XML data to the parser.
Best regards
Michael
"xmldev" <xmldev@.discussions.microsoft.com> wrote in message
news:8BD7E081-246C-47A8-8A53-C6FBDD157786@.microsoft.com...
> DECLARE @.MetaTagXML XML
> declare @.XmlDocumentHandler INT
> DECLARE @.QuestionName TABLE (
> Id INT IDENTITY(1,1),
> [metatag] [Varchar](100))
>
> SELECT @.MetaTagXML ='<Report ReportGUID
> ="CBFF8200-3B52-4F24-9D48-AC5458720B41">
> <MetaTags>
> <MetaTag>Awareness</MetaTag>
> <MetaTag>advertising</MetaTag>
> </MetaTags>
> <Variables>
> <Variable NodeID="7700">
> <MetaTags>
> <MetaTag>Evaluation</MetaTag>
> <MetaTag>account management/primary source experience</MetaTag>
> </MetaTags>
> </Variable>
> <Variable NodeID="7701">
> <MetaTags>
> <MetaTag>quality</MetaTag>
> <MetaTag>quality of account management</MetaTag>
> </MetaTags>
> </Variable>
> </Variables>
> </Report>'
> EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
> SELECT *
> FROM OPENXML (@.XmlDocumentHandler,
> '/Report/Variables/Variable/MetaTags',2)
> WITH (
> MetaTag varchar(100),
> NodeID varchar(100) '../@.NodeID'
> )
> EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
> i am getting the resultset like this:
> Evaluation 7700
> quality 7701
> I expect like this
>
> Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
> account management/primary source experience 7700
> CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> 1. how to get all records
> 2. how to reference reportguid
> Thanks in advance
> Deva
>
declare @.XmlDocumentHandlerINT
DECLARE@.QuestionName TABLE(
IdINT IDENTITY(1,1),
[metatag][Varchar](100))
SELECT @.MetaTagXML ='<Report ReportGUID
="CBFF8200-3B52-4F24-9D48-AC5458720B41">
<MetaTags>
<MetaTag>Awareness</MetaTag>
<MetaTag>advertising</MetaTag>
</MetaTags>
<Variables>
<Variable NodeID="7700">
<MetaTags>
<MetaTag>Evaluation</MetaTag>
<MetaTag>account management/primary source experience</MetaTag>
</MetaTags>
</Variable>
<Variable NodeID="7701">
<MetaTags>
<MetaTag>quality</MetaTag>
<MetaTag>quality of account management</MetaTag>
</MetaTags>
</Variable>
</Variables>
</Report>'
EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
SELECT*
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags',2)
WITH (
MetaTag varchar(100),
NodeID varchar(100) '../@.NodeID'
)
EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
i am getting the resultset like this:
Evaluation7700
quality7701
I expect like this
Evaluation7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
account management/primary source experience 7700
CBFF8200-3B52-4F24-9D48-AC5458720B41
quality7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
1. how to get all records
2. how to reference reportguid
Thanks in advance
Deva
You want one row per MetaTag element, so your row pattern in OpenXML needs
to select that element.
And you need to add a row for the ReportGUID:
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags/MetaTag',2)
WITH (
MetaTag varchar(100) '.',
NodeID varchar(100) '../../@.NodeID',
ReportID varchar(100) '../../../../@.ReportGUID'
)
Also note that since you are using OpenXML, using the XML datatype is not
necessarily beneficial, since sp_xml_preparedocument will serialize the XML
and reparse it. So using nvarchar(max) may be the better approach to pass
the XML data to the parser.
Best regards
Michael
"xmldev" <xmldev@.discussions.microsoft.com> wrote in message
news:8BD7E081-246C-47A8-8A53-C6FBDD157786@.microsoft.com...
> DECLARE @.MetaTagXML XML
> declare @.XmlDocumentHandler INT
> DECLARE @.QuestionName TABLE (
> Id INT IDENTITY(1,1),
> [metatag] [Varchar](100))
>
> SELECT @.MetaTagXML ='<Report ReportGUID
> ="CBFF8200-3B52-4F24-9D48-AC5458720B41">
> <MetaTags>
> <MetaTag>Awareness</MetaTag>
> <MetaTag>advertising</MetaTag>
> </MetaTags>
> <Variables>
> <Variable NodeID="7700">
> <MetaTags>
> <MetaTag>Evaluation</MetaTag>
> <MetaTag>account management/primary source experience</MetaTag>
> </MetaTags>
> </Variable>
> <Variable NodeID="7701">
> <MetaTags>
> <MetaTag>quality</MetaTag>
> <MetaTag>quality of account management</MetaTag>
> </MetaTags>
> </Variable>
> </Variables>
> </Report>'
> EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
> SELECT *
> FROM OPENXML (@.XmlDocumentHandler,
> '/Report/Variables/Variable/MetaTags',2)
> WITH (
> MetaTag varchar(100),
> NodeID varchar(100) '../@.NodeID'
> )
> EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
> i am getting the resultset like this:
> Evaluation 7700
> quality 7701
> I expect like this
>
> Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
> account management/primary source experience 7700
> CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> 1. how to get all records
> 2. how to reference reportguid
> Thanks in advance
> Deva
>
OPEN XML QUESTION
DECLARE @.MetaTagXML XML
declare @.XmlDocumentHandler INT
DECLARE @.QuestionName TABLE (
Id INT IDENTITY(1,1),
[metatag] [Varchar](100))
SELECT @.MetaTagXML ='<Report ReportGUID
="CBFF8200-3B52-4F24-9D48-AC5458720B41">
<MetaTags>
<MetaTag>Awareness</MetaTag>
<MetaTag>advertising</MetaTag>
</MetaTags>
<Variables>
<Variable NodeID="7700">
<MetaTags>
<MetaTag>Evaluation</MetaTag>
<MetaTag>account management/primary source experience</MetaTag>
</MetaTags>
</Variable>
<Variable NodeID="7701">
<MetaTags>
<MetaTag>quality</MetaTag>
<MetaTag>quality of account management</MetaTag>
</MetaTags>
</Variable>
</Variables>
</Report>'
EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags',2)
WITH (
MetaTag varchar(100),
NodeID varchar(100) '../@.NodeID'
)
EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
i am getting the resultset like this:
Evaluation 7700
quality 7701
I expect like this
Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
account management/primary source experience 7700
CBFF8200-3B52-4F24-9D48-AC5458720B41
quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
1. how to get all records
2. how to reference reportguid
Thanks in advance
DevaYou want one row per MetaTag element, so your row pattern in OpenXML needs
to select that element.
And you need to add a row for the ReportGUID:
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags/MetaTag',2)
WITH (
MetaTag varchar(100) '.',
NodeID varchar(100) '../../@.NodeID',
ReportID varchar(100) '../../../../@.ReportGUID'
)
Also note that since you are using OpenXML, using the XML datatype is not
necessarily beneficial, since sp_xml_preparedocument will serialize the XML
and reparse it. So using nvarchar(max) may be the better approach to pass
the XML data to the parser.
Best regards
Michael
"xmldev" <xmldev@.discussions.microsoft.com> wrote in message
news:8BD7E081-246C-47A8-8A53-C6FBDD157786@.microsoft.com...
> DECLARE @.MetaTagXML XML
> declare @.XmlDocumentHandler INT
> DECLARE @.QuestionName TABLE (
> Id INT IDENTITY(1,1),
> [metatag] [Varchar](100))
>
> SELECT @.MetaTagXML ='<Report ReportGUID
> ="CBFF8200-3B52-4F24-9D48-AC5458720B41">
> <MetaTags>
> <MetaTag>Awareness</MetaTag>
> <MetaTag>advertising</MetaTag>
> </MetaTags>
> <Variables>
> <Variable NodeID="7700">
> <MetaTags>
> <MetaTag>Evaluation</MetaTag>
> <MetaTag>account management/primary source experience</MetaTag>
> </MetaTags>
> </Variable>
> <Variable NodeID="7701">
> <MetaTags>
> <MetaTag>quality</MetaTag>
> <MetaTag>quality of account management</MetaTag>
> </MetaTags>
> </Variable>
> </Variables>
> </Report>'
> EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
> SELECT *
> FROM OPENXML (@.XmlDocumentHandler,
> '/Report/Variables/Variable/MetaTags',2)
> WITH (
> MetaTag varchar(100),
> NodeID varchar(100) '../@.NodeID'
> )
> EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
> i am getting the resultset like this:
> Evaluation 7700
> quality 7701
> I expect like this
>
> Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
> account management/primary source experience 7700
> CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> 1. how to get all records
> 2. how to reference reportguid
> Thanks in advance
> Deva
>
declare @.XmlDocumentHandler INT
DECLARE @.QuestionName TABLE (
Id INT IDENTITY(1,1),
[metatag] [Varchar](100))
SELECT @.MetaTagXML ='<Report ReportGUID
="CBFF8200-3B52-4F24-9D48-AC5458720B41">
<MetaTags>
<MetaTag>Awareness</MetaTag>
<MetaTag>advertising</MetaTag>
</MetaTags>
<Variables>
<Variable NodeID="7700">
<MetaTags>
<MetaTag>Evaluation</MetaTag>
<MetaTag>account management/primary source experience</MetaTag>
</MetaTags>
</Variable>
<Variable NodeID="7701">
<MetaTags>
<MetaTag>quality</MetaTag>
<MetaTag>quality of account management</MetaTag>
</MetaTags>
</Variable>
</Variables>
</Report>'
EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags',2)
WITH (
MetaTag varchar(100),
NodeID varchar(100) '../@.NodeID'
)
EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
i am getting the resultset like this:
Evaluation 7700
quality 7701
I expect like this
Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
account management/primary source experience 7700
CBFF8200-3B52-4F24-9D48-AC5458720B41
quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
1. how to get all records
2. how to reference reportguid
Thanks in advance
DevaYou want one row per MetaTag element, so your row pattern in OpenXML needs
to select that element.
And you need to add a row for the ReportGUID:
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags/MetaTag',2)
WITH (
MetaTag varchar(100) '.',
NodeID varchar(100) '../../@.NodeID',
ReportID varchar(100) '../../../../@.ReportGUID'
)
Also note that since you are using OpenXML, using the XML datatype is not
necessarily beneficial, since sp_xml_preparedocument will serialize the XML
and reparse it. So using nvarchar(max) may be the better approach to pass
the XML data to the parser.
Best regards
Michael
"xmldev" <xmldev@.discussions.microsoft.com> wrote in message
news:8BD7E081-246C-47A8-8A53-C6FBDD157786@.microsoft.com...
> DECLARE @.MetaTagXML XML
> declare @.XmlDocumentHandler INT
> DECLARE @.QuestionName TABLE (
> Id INT IDENTITY(1,1),
> [metatag] [Varchar](100))
>
> SELECT @.MetaTagXML ='<Report ReportGUID
> ="CBFF8200-3B52-4F24-9D48-AC5458720B41">
> <MetaTags>
> <MetaTag>Awareness</MetaTag>
> <MetaTag>advertising</MetaTag>
> </MetaTags>
> <Variables>
> <Variable NodeID="7700">
> <MetaTags>
> <MetaTag>Evaluation</MetaTag>
> <MetaTag>account management/primary source experience</MetaTag>
> </MetaTags>
> </Variable>
> <Variable NodeID="7701">
> <MetaTags>
> <MetaTag>quality</MetaTag>
> <MetaTag>quality of account management</MetaTag>
> </MetaTags>
> </Variable>
> </Variables>
> </Report>'
> EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
> SELECT *
> FROM OPENXML (@.XmlDocumentHandler,
> '/Report/Variables/Variable/MetaTags',2)
> WITH (
> MetaTag varchar(100),
> NodeID varchar(100) '../@.NodeID'
> )
> EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
> i am getting the resultset like this:
> Evaluation 7700
> quality 7701
> I expect like this
>
> Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
> account management/primary source experience 7700
> CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> 1. how to get all records
> 2. how to reference reportguid
> Thanks in advance
> Deva
>
open xml output in word
I has a sproc that creates xml output using 'Select ... for xml', but I want
my code to automatically open it in Word, so the user will see the output in
word and can save it whereever they want. I tried the xp_cmdShell (after
enabling it) - either I'm not using it correctly or something.
This is what I'm doing:
declare @.RV as integer
exec @.rv = uspCatalogtoXML
xp_cmdshell 'word.exe ' + @.RV
but I know the xp_cmdShell line is wrong.
Anyone help?
Reference: http://support.microsoft.com/kb/210565
2000,2003
Reference: http://office.microsoft.com/en-us/word/HP101640101033.aspx
2007
Depending on what version of word you will need to navigate to the proper
folder and the use the winword.exe...
example :xp_cmdshell 'C:\Program Files\Microsoft Office\Office\Winword.exe
/a /w'
Problem is, I do not believe you can pass in an XML file for 2000 or 2003.
In 2007 you can use the /pxslt sitch but you will have to save the xml and
the xslt as a file first.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Jane" <Jane@.discussions.microsoft.com> wrote in message
news:DC26E85E-CFFC-4A63-9989-C361B84634E0@.microsoft.com...
>I has a sproc that creates xml output using 'Select ... for xml', but I
>want
> my code to automatically open it in Word, so the user will see the output
> in
> word and can save it whereever they want. I tried the xp_cmdShell (after
> enabling it) - either I'm not using it correctly or something.
> This is what I'm doing:
> declare @.RV as integer
> exec @.rv = uspCatalogtoXML
> xp_cmdshell 'word.exe ' + @.RV
> but I know the xp_cmdShell line is wrong.
> Anyone help?
>
my code to automatically open it in Word, so the user will see the output in
word and can save it whereever they want. I tried the xp_cmdShell (after
enabling it) - either I'm not using it correctly or something.
This is what I'm doing:
declare @.RV as integer
exec @.rv = uspCatalogtoXML
xp_cmdshell 'word.exe ' + @.RV
but I know the xp_cmdShell line is wrong.
Anyone help?
Reference: http://support.microsoft.com/kb/210565
2000,2003
Reference: http://office.microsoft.com/en-us/word/HP101640101033.aspx
2007
Depending on what version of word you will need to navigate to the proper
folder and the use the winword.exe...
example :xp_cmdshell 'C:\Program Files\Microsoft Office\Office\Winword.exe
/a /w'
Problem is, I do not believe you can pass in an XML file for 2000 or 2003.
In 2007 you can use the /pxslt sitch but you will have to save the xml and
the xslt as a file first.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Jane" <Jane@.discussions.microsoft.com> wrote in message
news:DC26E85E-CFFC-4A63-9989-C361B84634E0@.microsoft.com...
>I has a sproc that creates xml output using 'Select ... for xml', but I
>want
> my code to automatically open it in Word, so the user will see the output
> in
> word and can save it whereever they want. I tried the xp_cmdShell (after
> enabling it) - either I'm not using it correctly or something.
> This is what I'm doing:
> declare @.RV as integer
> exec @.rv = uspCatalogtoXML
> xp_cmdshell 'word.exe ' + @.RV
> but I know the xp_cmdShell line is wrong.
> Anyone help?
>
open xml output in word
I has a sproc that creates xml output using 'Select ... for xml', but I want
my code to automatically open it in Word, so the user will see the output in
word and can save it whereever they want. I tried the xp_cmdShell (after
enabling it) - either I'm not using it correctly or something.
This is what I'm doing:
declare @.RV as integer
exec @.rv = uspCatalogtoXML
xp_cmdshell 'word.exe ' + @.RV
but I know the xp_cmdShell line is wrong.
Anyone help?Reference: http://support.microsoft.com/kb/210565
2000,2003
Reference: http://office.microsoft.com/en-us/w...1640101033.aspx
2007
Depending on what version of word you will need to navigate to the proper
folder and the use the winword.exe...
example :xp_cmdshell 'C:\Program Files\Microsoft Office\Office\Winword.exe
/a /w'
Problem is, I do not believe you can pass in an XML file for 2000 or 2003.
In 2007 you can use the /pxslt sitch but you will have to save the xml and
the xslt as a file first.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Jane" <Jane@.discussions.microsoft.com> wrote in message
news:DC26E85E-CFFC-4A63-9989-C361B84634E0@.microsoft.com...
>I has a sproc that creates xml output using 'Select ... for xml', but I
>want
> my code to automatically open it in Word, so the user will see the output
> in
> word and can save it whereever they want. I tried the xp_cmdShell (after
> enabling it) - either I'm not using it correctly or something.
> This is what I'm doing:
> declare @.RV as integer
> exec @.rv = uspCatalogtoXML
> xp_cmdshell 'word.exe ' + @.RV
> but I know the xp_cmdShell line is wrong.
> Anyone help?
>
my code to automatically open it in Word, so the user will see the output in
word and can save it whereever they want. I tried the xp_cmdShell (after
enabling it) - either I'm not using it correctly or something.
This is what I'm doing:
declare @.RV as integer
exec @.rv = uspCatalogtoXML
xp_cmdshell 'word.exe ' + @.RV
but I know the xp_cmdShell line is wrong.
Anyone help?Reference: http://support.microsoft.com/kb/210565
2000,2003
Reference: http://office.microsoft.com/en-us/w...1640101033.aspx
2007
Depending on what version of word you will need to navigate to the proper
folder and the use the winword.exe...
example :xp_cmdshell 'C:\Program Files\Microsoft Office\Office\Winword.exe
/a /w'
Problem is, I do not believe you can pass in an XML file for 2000 or 2003.
In 2007 you can use the /pxslt sitch but you will have to save the xml and
the xslt as a file first.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Jane" <Jane@.discussions.microsoft.com> wrote in message
news:DC26E85E-CFFC-4A63-9989-C361B84634E0@.microsoft.com...
>I has a sproc that creates xml output using 'Select ... for xml', but I
>want
> my code to automatically open it in Word, so the user will see the output
> in
> word and can save it whereever they want. I tried the xp_cmdShell (after
> enabling it) - either I'm not using it correctly or something.
> This is what I'm doing:
> declare @.RV as integer
> exec @.rv = uspCatalogtoXML
> xp_cmdshell 'word.exe ' + @.RV
> but I know the xp_cmdShell line is wrong.
> Anyone help?
>
Open Xml
Hi All,
I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.
Definitely doing something wrong here, please advise!
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
SELECT *
FROM
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) 'http://http://DVName',
DvcodeNo int 'http://http://DvcodeNo',
CustSurveyNo int 'http://../CustSurveyNo',
ClaimGroupType varchar (20) 'http://../ClaimGroupType',
QuestionID int 'http://QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @.docHandle
The reason you are only getting two rows in the resultset is because there are only two nodes that match your xpath expression /DivisionName/ClaimGroup/Questions/Answer/Ansoption.
What is the result set that you desire? I'm not certain what the results are you implying when you say you are expecting 4 rows.
Regards,
Galex
|||Thanks for the reply first of all, luckily I have sorted it out now.
Thanks again
|||Hi
I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml
<skill>
<Coach type="AA">false</Coach>
<CDT type="AA">false</CDT>
<DIT type="AA">false</DIT>
<FSA type="AA">false</FSA>
<LMR type="AA">false</LMR>
<ROSPA type="Additional">false</ROSPA>
<IAM type="Additional">false</IAM>
<Diamond type="Additional">false</Diamond>
<DipDI type="Additional">false</DipDI>
<BTEC type="Additional">false</BTEC>
<QEF type="Additional">
<member>false</member>
<certificatenumber /><level />
</QEF>
<BTec type="Additional">false</BTec>
</skill>
Srini
|||Can you be more specific in your question? E.g. what element you want to access? After you have access, what do you want to do about that element?|||This is the column in a table that contains the skill set of an individual candidate. on Passing the candidate id , i would access the access and the output i would give is like
coach=false, cdt=false,dit=false,fsa=false, lmr=false,iam=false,qefmemer=false,qefcertificatenumber=null,btec=false.
i would like to query for basic qualification and additional qualifications.
Thanks
Srini
|||
Anonymous wrote:
Hi
I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml
<skill>
<Coach type="AA">false</Coach>
<CDT type="AA">false</CDT>
<DIT type="AA">false</DIT>
<FSA type="AA">false</FSA>
<LMR type="AA">false</LMR>
<ROSPA type="Additional">false</ROSPA>
<IAM type="Additional">false</IAM>
<Diamond type="Additional">false</Diamond>
<DipDI type="Additional">false</DipDI>
<BTEC type="Additional">false</BTEC>
<QEF type="Additional">
<member>false</member>
<certificatenumber /><level />
</QEF>
<BTec type="Additional">false</BTec>
</skill>
Srini
<xsl:for-each select="skill/coach/{@.type}>
</xsl:for-each>
sqlOpen Xml
Hi All,
I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.
Definitely doing something wrong here, please advise!
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
SELECT *
FROM
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) 'http://http://DVName',
DvcodeNo int 'http://http://DvcodeNo',
CustSurveyNo int 'http://../CustSurveyNo',
ClaimGroupType varchar (20) 'http://../ClaimGroupType',
QuestionID int 'http://QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @.docHandle
The reason you are only getting two rows in the resultset is because there are only two nodes that match your xpath expression /DivisionName/ClaimGroup/Questions/Answer/Ansoption.
What is the result set that you desire? I'm not certain what the results are you implying when you say you are expecting 4 rows.
Regards,
Galex
|||Thanks for the reply first of all, luckily I have sorted it out now.
Thanks again
|||Hi
I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml
<skill>
<Coach type="AA">false</Coach>
<CDT type="AA">false</CDT>
<DIT type="AA">false</DIT>
<FSA type="AA">false</FSA>
<LMR type="AA">false</LMR>
<ROSPA type="Additional">false</ROSPA>
<IAM type="Additional">false</IAM>
<Diamond type="Additional">false</Diamond>
<DipDI type="Additional">false</DipDI>
<BTEC type="Additional">false</BTEC>
<QEF type="Additional">
<member>false</member>
<certificatenumber /><level />
</QEF>
<BTec type="Additional">false</BTec>
</skill>
Srini
|||Can you be more specific in your question? E.g. what element you want to access? After you have access, what do you want to do about that element?|||This is the column in a table that contains the skill set of an individual candidate. on Passing the candidate id , i would access the access and the output i would give is like
coach=false, cdt=false,dit=false,fsa=false, lmr=false,iam=false,qefmemer=false,qefcertificatenumber=null,btec=false.
i would like to query for basic qualification and additional qualifications.
Thanks
Srini
|||Hi
I am new to xml in sql server. If i have an xml of this type.how do i access the elements in the xml
<skill>
<Coach type="AA">false</Coach>
<CDT type="AA">false</CDT>
<DIT type="AA">false</DIT>
<FSA type="AA">false</FSA>
<LMR type="AA">false</LMR>
<ROSPA type="Additional">false</ROSPA>
<IAM type="Additional">false</IAM>
<Diamond type="Additional">false</Diamond>
<DipDI type="Additional">false</DipDI>
<BTEC type="Additional">false</BTEC>
<QEF type="Additional">
<member>false</member>
<certificatenumber /><level />
</QEF>
<BTec type="Additional">false</BTec>
</skill>
Srini
<xsl:for-each select="skill/coach/{@.type}>
</xsl:for-each>
OPEN XML
I have to insert one table by using the concept OPENXML
But i want to insert a table lot of fields i require, in the open XML few fields fields are there , so i want to selet some fields from other table
can u guide me for this scenario to INSERT some fields from one table and some in OPENXML as a single insert statement
Awaiting for Reply PLease
Thanx
Consider using the openXml to create a temporary table first. Then join the two tables as the base for the insert.
Monday, February 20, 2012
Online/Offline Application that must synchronize with SQL 2005
Hi !
I must developp a WPF Application with online and offline capabilities! First I think to use XML file on the local application and transfer these XML files to a webservice that will synchronize them with the SQL 2005 Server
BUT
I read about "Replication"... and I think it will be much simpler to implement!!!
Do you think it is a good idea to have a "local" SQL Express database and replicate it (when connection available is) with the principal database that will run a standard SQL 2005 version!
Do you have another suggestion to make such an application?
Thanks for help!!!
PlaTyPuS
PS: when the sql express solution a good idea is, does it give a simple solution to programm an automatic synchronization every hour?
Yes, you can use replication to sync data from the "remote" server to "local" server. You can schedule the job run in certain interval or even continuously.
You might want to take a look Microsoft Synchronization Services for ADO.NET. It might be exactly what you want. The CTP release can be downloaded from here (http://www.microsoft.com/downloads/details.aspx?FamilyID=75FEF59F-1B5E-49BC-A21A-9EF4F34DE6FC&displaylang=en). Rather than simply replicating database data, it provides a set of API to sync between data services and a data store. There are also more discussion in another MSDN forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1225&SiteID=1
|||Yes, you can use replication to sync data from the "remote" server to "local" server. You can schedule the job run in certain interval or even continuously.
You might want to take a look Microsoft Synchronization Services for ADO.NET. It might be exactly what you want. The CTP release can be downloaded from here (http://www.microsoft.com/downloads/details.aspx?FamilyID=75FEF59F-1B5E-49BC-A21A-9EF4F34DE6FC&displaylang=en). Rather than simply replicating database data, it provides a set of API to sync between data services and a data store. There are also more discussion in another MSDN forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1225&SiteID=1
thx for answer! very interesting links 
(the blog of Rafik is very very interesting!!! : http://blogs.msdn.com/synchronizer/archive/2007/03/01/sync-demos-write-up.aspx)
++
Subscribe to:
Posts (Atom)