Tag: xml

Text XML to Sql Table… there and back again

I have a table: with XMLData as ( SELECT uid, commonname, cast(labeldetails as XML) labelxml FROM [MyLables] ) SELECT uid ,commonname ,labelxml FROM XMLData [x] I get (condensed for brevity): B8A3DF5E OrderType1 <NewDataSet><LabelData><Name>1d Vert</Name><Column>… 9D0F94C7 OrderType2 <NewDataSet><LabelData><Name>ItemNumber1D</Name><Co… Inside Label Details is XML Data <NewDataSet> <LabelData> <Name>mol</Name> <Column>mol</Column> <Type>MOLIMAGE</Type> <xpos>510</xpos> <ypos>110</ypos> <width>auto</width> <height>auto</height> <Font>Arial</Font> <Fontsize>10.0</Fontsize> <FontStyle>Normal</FontStyle> […]

Select data from XML in SQL

I am having trouble in getting some data from a XML text saved in my SQL Server DB. My data is as follow: <retConsReciCTe xmlns=”http://www.portalfiscal.inf.br/cte” versao=”3.00″> <tpAmb>2</tpAmb> <verAplic>SP-CTe-25-05-2017</verAplic> <nRec>351000011484798</nRec> <cStat>104</cStat> <xMotivo>Lote processado</xMotivo> <cUF>35</cUF> <protCTe versao=”3.00″> <infProt> <tpAmb>2</tpAmb> <verAplic>SP-CTe-25-05-2017</verAplic> <chCTe>35170623838348000310570000000058731000058731</chCTe> <dhRecbto>2017-06-22T21:28:18-03:00</dhRecbto> <cStat>232</cStat> <xMotivo>Rejeição: IE do destinatário não informada</xMotivo> </infProt> </protCTe> </retConsReciCTe>

XML assigned to SqlXml parameter got extra-encoded?

The following code passes an XML parameter to a stored procedure. The SqlXml is converted from a string. var range = GetXml(“<Range><Column Name=\”Id\” Low=\”5002\” High=\”8076\” /></Range>”); cmd.Parameters.Add(new SqlParameter(“@Range”, SqlDbType.Xml) { Value = range }); SqlXml GetXml(string s) { var memoryStream = new MemoryStream(); var settings = new XmlWriterSettings(); settings.OmitXmlDeclaration = true; settings.ConformanceLevel = ConformanceLevel.Fragment; using […]

How to get tag attribute value from xml

I have a xml where the values are given: <User version=”5.1.0.1″ … (omitted for brevity) > <Login usewinLogin=”true” passwordNeverExpires=”false” /> <Misc /> <AdditionalInfo /> <OutOfOfficeSettings isOutOfOffice=”false” startDateTime=”0001-01-01T00:00:00.0000000Z” /> <RegionalSettings language=”de” culture=”de” /> </User>` I would need the value from OutofOfficeSettings isOutofOffice= (Options are false or true). I never did this before, is someone here to […]

Replacing Node name in an XML thats stored in a SQL Server database column

I’d like to know how I can replace a child node name in a xml that I stored in my SQL Server database Example XML <CompanyStatus> <ProductionServers> <ProductionServer> <Patch>0</Patch> <Status>Green</Status> <Test_Node>Yes</Test_Node> </ProductionServers> </ProductionServer> </CompanyStatus> How would I change that to the following: <CompanyStatus> <ProductionServers> <ProductionServer> <Patch>0</Patch> <Status>Green</Status> <Live_Node>Yes</Live_Node> </ProductionServers> </ProductionServer> </CompanyStatus> Where essentially the only […]

Combine Multiple Rows of XML Data into One XML Value (SQL Server)

I have a table with two columns like this (DataXML is xml data type): RowID DataXML ––––– ––––––––––––––––––––––––––– 1 <ELEM FOO=”11″ BAR=”A” /> 2 <ELEM FOO=”22″ BAR=”B” /> 3 <ELEM FOO=”33″ BAR=”C” /> 4 <ELEM FOO=”44″ BAR=”D” /> I would like to write a T-SQL statement something like this: declare @MyXML xml; set @MyXML = […]

Abnormal data validation in XML

I will be receiving some confidential employee data in XML with separate XSD. It will be injected into our SQL Server (2016) using SSIS. I was asked to edit the XSD schema as I want (for the data formats, i.e. change string to int, etc.). I wish I had some data validation, like if I […]

Retrieve complex XML from SQL Server 2016

Given these tables: DECLARE @Documents TABLE ( document_id int, document_file varchar(200), description varchar(200), pages int, write_barcode bit ) DECLARE @Data TABLE ( id int, tax_id varchar(100), bo_lgl_name varchar(200), document_id int, keydata varchar(100), field_name varchar(100), value varchar(100) ) populated with this data: INSERT INTO @Data VALUES (1, ‘123456789’, ‘Fred Flintstone’, 269, Null, ‘LastName’, ‘Smith’) INSERT INTO […]

Convert table to XML with attributes using T-SQL

I am trying to convert the tables: tblCustomAttributeSourceSchema that contains the COLUMN DEFINITIONS and tblLabelAttributes that contains the VALUES for each of the columns For your convenience I created the tables in this sqlfiddle: http://www.sqlfiddle.com/#!6/b2fde/1 I would like to convert this to a table containing “LabelID” (of type INT – originally from tblLabelAttributes) and “XML_VALUE” […]

SQL Querying From XML

I’m trying to query an XML file, to extract user/license information. I seem to be stuck though. Below is my data set: <?xml version=”1.0″ encoding=”UTF-16″?> <Users> <User> <UserName>Elise</UserName> <IsConnected>1</IsConnected> <Modules> <Module> <KeyType>LOGISTICS-LTD</KeyType> <KeyDesc>Limited Logistics User</KeyDesc> <DbType>MSS</DbType> <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules> <ReferingCount>0</ReferingCount> <InstallNo>0020534918</InstallNo> </Module> <Module> <KeyType>SAP-ADDONS</KeyType> <KeyDesc>SAP AddOns</KeyDesc> <DbType>MSS</DbType> <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules> <ReferingCount>0</ReferingCount> <InstallNo>0020534918</InstallNo> </Module> <Module> <KeyType>SAP0000007050</KeyType> <KeyDesc>Workflow</KeyDesc> <DbType>MSS</DbType> <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules> <ReferingCount>0</ReferingCount> […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.